Today, in Episode #1449, Mike "ExcelisFun" Girvin and Bill "MrExcel" Jelen address different methods to answer the question: "Is there a way that the Grand Totals and Subtotals would omit the Negative Subtotals?"
Dueling Excel Podcast #87...This episode is the video podcast companion to the book, "Slaying Excel Dragons: A Beginners Guide to Conquering Excel's Frustrations and Making Excel Fun", by Mike Girvin and Bill Jelen. [Currently available in eBook / Print Edition and as a DVD Bundle]
and
the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions
Dueling Excel Podcast #87...This episode is the video podcast companion to the book, "Slaying Excel Dragons: A Beginners Guide to Conquering Excel's Frustrations and Making Excel Fun", by Mike Girvin and Bill Jelen. [Currently available in eBook / Print Edition and as a DVD Bundle]
and
the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions
Transcript of the video:
Bill Jelen: Hey, welcome back. It's another dueling Excel podcast.
I'm Bill Jelen from MrExcel. We’ll be joined by Mike Girvin from Excel Is Fun.
This is episode 87, Positive Subtotals.
All right, from YouTube.
I want to know if there's way that the grand total and subtotals would omit the negative subtotals – not the negative values, the negative subtotal.
So he's talking about Data, Subtotal, at each change in Region, let's say, and then sales.
So anytime that the total of the region is negative, like for example, West.
We want to exclude that.
Wow, okay.
So this is funny because every time I'm talking about pivot tables, I complain about the following functionality.
And it's actually going to work in my favour this time.
I love that.
I've complained about this hundreds of times.
Well now, I finally found the use where it's good.
We're going to take this data, insert a Pivot Table, click OK.
Let's put Region down the left-hand side, and Sales in the value area.
And you see, we get all of these items.
But now, that's all I'm going to do.
I'm going to use the filter.
I'm going to do a Value Filter and say, greater than or equal to 0.
Click OK.
And my complaint with this, see we have 341.
Click OK.
After doing the filter, it shows us the total of only the visible cells.
I'm doing a top 5 or a top 10 report.
I really want to see the total of all of the customers, and so I have lots of tricks to get around that but in this case, it's working out perfectly.
It gives me the grand total without the negative values.
All right.
Mike, let's see what you have.
Mike Girvin: Thanks MrExcel.
Yes, the pivot table is the way to go.
Just what?
A couple clicks and boom!
You have it.
I guess if you wanted to keep the subtotals, then you got to figure out how to get rid of the negative subtotals, all right.
So if you have a data set, the trick to subtotals that uses that, you sort before you do subtotals.
All right, click in a single cell.
I'm going to use the keyboard shortcut, Alt+D+B, at each change in Region.
At each change in means, it looks for a change in the sorted list and inserts a row.
You'll have a blank cell, the word Total, and then the actual subtotal function.
We want to Sum on that particular field.
Click OK, all right.
So blank to East Total and then the subtotal function.
And down here to the bottom and sure enough, subtotal is adding this one, this one, and the negative ones.
And so we need to get rid of that.
Now I would just click in the cell and put a new formula, but I think I'll put it right here.
So this will be a Total without Negative, right?
And in 2007 and 2010, there's a great new function, SUMIFS, with an S. And it's great because you can have more than one criteria.
Well our sum_range, I'm going to highlight because we need to eventually get to look at just the subtotals, and then find just the subtotals without a negative comma.
Now the criteria.
How do I isolate just the subtotals without a negative?
Well, I'm going to notice that the subtotal feature puts in a column with the word Total or a blank column.
So I'm going to start with criteria range and I'm going to highlight the same parallel range here, and comma, and then I need to look for the word Total.
Well look, it's says West Total and Midwest Total.
=SUMIFS(C6:C29,B6:B29) So I need to do an approximate search.
So here's how you do it.
You go double quotes, and asterisk means zero or more characters, and then the word Total and double quote, =SUMIFS(C6:C29,B6:B29,"*Total").
So this says, please find any text string with Total at the end.
Now that'll just give me the actual cells in this range that have the subtotal, but I need to specify one further criteria in this range here.
I just need to say now find only values that are less than zero, =SUMIFS(C6:C29,B6:B29,"*Total",C6:C29,"<0").
All right.
So this first criteria here would give me just the three, and once it has those, now it's going to eliminate any ones that are less than zero.
I'm going to Ctrl Enter, and that's not less than, it’s greater than because it was adding just the negative ones.
Now it's greater than zero, =SUMIFS(C6:C29,B6:B29,"*Total",C6:C29,">0"), and that will work.
Now let’s just scoop this out here.
We could alternatively, if we didn't want to do the Total, we could use that same setup as except for change one of the criterias.
Now notice this is green.
I can actually edit a formula by grabbing the edge and dragging it over.
So now I'm going to look for not the cells with Total but the blank cells.
And the criteria for empty cell but blank could be a lots of things, but empty meaning nothing in it is equal sign, =SUMIFS(C6:C29,B6:B29,"=",C6:C29,"<0").
So that's another way.
Still further, if you had, if you didn't have 2007 or 2010, then you're going to be in trouble here.
I think if I put that right there, you could use the SUMPRODUCT.
Now you could say is blank here, convert the trues and falses to ones and zeros, which some product needs, and then you can do that same range.
Anything greater than zero and double negatives to convert it to ones and zeros, and then this is the range of values.
When it gets a 1 times 1 times the value, then the SUM part of SUMPRODUCT adds.
=SUMPRODUCT(--ISBLANK(A6:A29),--C6:C29>0,C6:C29) Alternatively you could do, look for Total.
=SUMPRODUCT(--ISNUMBER(SEARCH("Total",B6:B29)),--(C6:C29>0),C6:C29).
I wouldn't do this one.
I like that one with the blanks.
They are much better but SEARCH looks for subtext strings within a text string.
Once it returns the position that found the word Total, you would then need to find out if it's a number, all right.
So that's probably not a very good one, all right.
SUMIFS or SUMPRODUCT with is blank.
All right, I’ll throw it back to MrExcel.
Bill Jelen: Hey.
All right, Mike.
SUMIFS, SUMPRODUCT and SUMBLANK – what a cool way to go just like the pivot table though.
Like you said, I took the good one first.
Hey, I want to thank everyone for stopping by.
We'll see you next time for another duelling Excel podcast from MrExcel and Excel Is Fun.
I'm Bill Jelen from MrExcel. We’ll be joined by Mike Girvin from Excel Is Fun.
This is episode 87, Positive Subtotals.
All right, from YouTube.
I want to know if there's way that the grand total and subtotals would omit the negative subtotals – not the negative values, the negative subtotal.
So he's talking about Data, Subtotal, at each change in Region, let's say, and then sales.
So anytime that the total of the region is negative, like for example, West.
We want to exclude that.
Wow, okay.
So this is funny because every time I'm talking about pivot tables, I complain about the following functionality.
And it's actually going to work in my favour this time.
I love that.
I've complained about this hundreds of times.
Well now, I finally found the use where it's good.
We're going to take this data, insert a Pivot Table, click OK.
Let's put Region down the left-hand side, and Sales in the value area.
And you see, we get all of these items.
But now, that's all I'm going to do.
I'm going to use the filter.
I'm going to do a Value Filter and say, greater than or equal to 0.
Click OK.
And my complaint with this, see we have 341.
Click OK.
After doing the filter, it shows us the total of only the visible cells.
I'm doing a top 5 or a top 10 report.
I really want to see the total of all of the customers, and so I have lots of tricks to get around that but in this case, it's working out perfectly.
It gives me the grand total without the negative values.
All right.
Mike, let's see what you have.
Mike Girvin: Thanks MrExcel.
Yes, the pivot table is the way to go.
Just what?
A couple clicks and boom!
You have it.
I guess if you wanted to keep the subtotals, then you got to figure out how to get rid of the negative subtotals, all right.
So if you have a data set, the trick to subtotals that uses that, you sort before you do subtotals.
All right, click in a single cell.
I'm going to use the keyboard shortcut, Alt+D+B, at each change in Region.
At each change in means, it looks for a change in the sorted list and inserts a row.
You'll have a blank cell, the word Total, and then the actual subtotal function.
We want to Sum on that particular field.
Click OK, all right.
So blank to East Total and then the subtotal function.
And down here to the bottom and sure enough, subtotal is adding this one, this one, and the negative ones.
And so we need to get rid of that.
Now I would just click in the cell and put a new formula, but I think I'll put it right here.
So this will be a Total without Negative, right?
And in 2007 and 2010, there's a great new function, SUMIFS, with an S. And it's great because you can have more than one criteria.
Well our sum_range, I'm going to highlight because we need to eventually get to look at just the subtotals, and then find just the subtotals without a negative comma.
Now the criteria.
How do I isolate just the subtotals without a negative?
Well, I'm going to notice that the subtotal feature puts in a column with the word Total or a blank column.
So I'm going to start with criteria range and I'm going to highlight the same parallel range here, and comma, and then I need to look for the word Total.
Well look, it's says West Total and Midwest Total.
=SUMIFS(C6:C29,B6:B29) So I need to do an approximate search.
So here's how you do it.
You go double quotes, and asterisk means zero or more characters, and then the word Total and double quote, =SUMIFS(C6:C29,B6:B29,"*Total").
So this says, please find any text string with Total at the end.
Now that'll just give me the actual cells in this range that have the subtotal, but I need to specify one further criteria in this range here.
I just need to say now find only values that are less than zero, =SUMIFS(C6:C29,B6:B29,"*Total",C6:C29,"<0").
All right.
So this first criteria here would give me just the three, and once it has those, now it's going to eliminate any ones that are less than zero.
I'm going to Ctrl Enter, and that's not less than, it’s greater than because it was adding just the negative ones.
Now it's greater than zero, =SUMIFS(C6:C29,B6:B29,"*Total",C6:C29,">0"), and that will work.
Now let’s just scoop this out here.
We could alternatively, if we didn't want to do the Total, we could use that same setup as except for change one of the criterias.
Now notice this is green.
I can actually edit a formula by grabbing the edge and dragging it over.
So now I'm going to look for not the cells with Total but the blank cells.
And the criteria for empty cell but blank could be a lots of things, but empty meaning nothing in it is equal sign, =SUMIFS(C6:C29,B6:B29,"=",C6:C29,"<0").
So that's another way.
Still further, if you had, if you didn't have 2007 or 2010, then you're going to be in trouble here.
I think if I put that right there, you could use the SUMPRODUCT.
Now you could say is blank here, convert the trues and falses to ones and zeros, which some product needs, and then you can do that same range.
Anything greater than zero and double negatives to convert it to ones and zeros, and then this is the range of values.
When it gets a 1 times 1 times the value, then the SUM part of SUMPRODUCT adds.
=SUMPRODUCT(--ISBLANK(A6:A29),--C6:C29>0,C6:C29) Alternatively you could do, look for Total.
=SUMPRODUCT(--ISNUMBER(SEARCH("Total",B6:B29)),--(C6:C29>0),C6:C29).
I wouldn't do this one.
I like that one with the blanks.
They are much better but SEARCH looks for subtext strings within a text string.
Once it returns the position that found the word Total, you would then need to find out if it's a number, all right.
So that's probably not a very good one, all right.
SUMIFS or SUMPRODUCT with is blank.
All right, I’ll throw it back to MrExcel.
Bill Jelen: Hey.
All right, Mike.
SUMIFS, SUMPRODUCT and SUMBLANK – what a cool way to go just like the pivot table though.
Like you said, I took the good one first.
Hey, I want to thank everyone for stopping by.
We'll see you next time for another duelling Excel podcast from MrExcel and Excel Is Fun.
