MrExcel's Learn Excel #641 - Conditionally Summing

If you like this content, please consider visiting the video on YouTube and subscribe to the MrExcel Channel to show your support!
This video has been published on Mar 26, 2009.
Patrick asks how he can summarize his monthly time sheet to create a pie chart by department. Using the SUMIF function provides the step to make this relatively easy. Episode 641 shows you how.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
maxresdefault.jpg


Transcript of the video:
Hey welcome back to the MrExcel netcast. I'm Bill Jelen.
Today we have a question sent in by Patrick.
Patrick keeps track of his time spent each day.
Can I have a little time sheet here and he has the time in Column B.
And then in column C, If this is time that he spent helping another department.
He puts the abbreviation for that department.
He says at the end of the month he would like to build a pie chart, showing how much time he spent for other departments, but ignoring all of the let's say, internal time.
And he said you know, right now, he's doing that by hand basically adding it up.
I said. Well hey, if you have a complete list of the departments.
If you know which departments would possibly come up in the pie chart.
Just put those over in a blank section of the spreadsheet.
A list of all those departments.
And then what we can do is use the SUMIF function.
Now normally when we build the SUMIT function it wants to know what range we're interested in looking at.
And rather than hard code that I'm going from C to down to wherever the last row happens to be, today I just said I want to look at everything from C:C.
In other words the entire column, and check to see if it's equal to this department here in G4.
If it is add up the corresponding value from column B:B.
That says all of column B and what that will do is that will go through and look for this and see that we spent 0.4 hours on the ATAC department.
And then ofcourse it's easy to copy that down and have everything work perfectly.
Once we have that set up on the right hand side, and then ofcourse it's easy to go through and create a pie chart.
Just show those values.
Now I thought instead of actually showing percentages here. I did go through and change the "Chart options" to have "Data Labels" show the "Category name", that's the department, and the "Value" So what we can actually see is how many hours were spent on each department instead of a percentage.
I'm not sure the percentage is necessarily that useful.
You know hey, you know accounting department, I spent fourteen percent of my time on you.
It's more meaningful to say hey, I spent 27 hours last week on you or 27 hours last month on you.
So I did make that change and then also went through and change the default colors the excel uses.
You know easy to do. First click selects the whole pie.
Second click selects just one wedge of the pie.
And then you right click "Format Data Point" and choose a different color so you can very easily customize this to use more vibrant colors than what excel normally does.
Hey, thanks for stopping by. We'll see you next time for another netcast from MrExcel.
 

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top