John has figured out how to use SUMIF to find all entries less than 10. But his next step is to find everything between 10 & 20. He asks how can he make SUMIF handle two conditions?
In this video:
How to switch from SUMIF to SUMIFS
How to prevent having to edit each formula
How to use a Pivot Table to do the analysis quickly
And old-school SUMPRODUCT formula.
In this video:
How to switch from SUMIF to SUMIFS
How to prevent having to edit each formula
How to use a Pivot Table to do the analysis quickly
And old-school SUMPRODUCT formula.
Transcript of the video:
Learn Excel from MrExcel podcast episode 2311.
Sum if between 10 and 20.
Hey, welcome back to MrExcel Netcast. I'm Bill Jelen.
Today, a question from John.
John is relatively new to spreadsheets but he had a great question.
Today he's trying to categorize is spending into buckets let's say and John came up with this beautiful formula.
Here he says hey look through the cost column, see if it's less than 10 and then add up a corresponding cost column.
Great use for SUMIF but then next row John asks: Hey how can I make this do greater than 10 and less than 20?
I don't know what version of Excel John is using.
I said, see if you have SUMIFS - plural. And SUMIFS is a little bit backwards. =SUMIFS(.
First thing you have to do is what you want to add up, so that's column B over here.
And then it's pairs of things.
Go look through this column and see if it's equal to this criterion.
Then go look through this column and see if it's this criterion.
Now in this case it should be the same column every single time, so we're going to look through this column, comma.
And see if it's greater than or equal to 10 so >= to 10 like that.
For the second criteria: go look at that same column again.
And see if it's less than.
20.
Alright, and hey, even better than that, SUMIFS can keep going on, so if we needed, let's say we gotta come over here to product and we only wanted to know the photography product, we could say with that column is equal to photography you could have...
I think, 126 pairs of check this column for this criterion.
In this case we just need two like that. Alright, so that gets us the value.
Now the hassle with the way that I built this so far is that we're going to hard code this greater than 10. Less than 20.
I'm going to pause the video for a second.
Alright, so here's a nice flexible formula that does the same thing.
Looked through the cost column, see if the cost column is greater than or equal to, then then ampersand.
That is a concatenation character greater than equal to 10, and then look through the cost column and see if it's less than the next row, which is 20.
And the beautiful thing about this is once we enter it, we just copy it down and we get the answers for everything without having to edit the formula in each cell.
COUNTIFS does what SUMIFS does we're looking for this criteria, spending cost greater than equal to 10, spending costs less than D4. Copy that down.
Hey John, SUMIFS, countifs.
When I was starting with spreadsheets, we didn't have that.
We had SUMIF or COUNTIF there's a lot of different ways to do things in Excel though, and one way that would be a great way to solve this if your spreadsheet has pivot tables, select one cell in your data back here.
Insert pivot table. I'll let it go to a new worksheet.
That's OK, it shows up here on this worksheet and we can build a report here.
Now let's see.
Pivot tables generally start out looking like this, so columns across the top rows down the side.
And this is very unusual.
I'm going to take that cost field and drag it down the side like this and then take the cost field three times to the values area and the first one.
I'm gonna let it be a sum.
For the second one: Now I'm going to double click the heading and say I want to see a count.
Then over here I'm going to double click and I want to see an Average.
Now this report isn't very fascinating 'cause it's looking at every single dollar amount.
Choose one of those dollar amounts. Then group field.
So this is PivotTable, Analyze, Go to Group Field.
Say we want a group, let's say from zero up to 100 based on ten.
Click OK.
Man that is awesome and so for each bucket we know the total cost, how many there were and what the average was.
Right click here for more number format currency 2 decimal places.
I guess makes sense. So pivot table. Great way to go.
Another way, awesome formula here.
This is definitely before we had SUMIFS we would do this take all these amounts over in column B, multiply multiply. This has to be in parentheses.
See if the amount is greater than equal to zero and then multiply. See if the amount is less than 10.
If both of these are true, it's the amount times 1 times 1 and you get the amount.
If either one is false, it gets wiped out to 0, right? So there's lots of different ways to solve this.
SUMIFS, though, great, great, beautiful thing to learn.
Now SUMIFS of course covered in this book, MrExcel LX the Holy Grail of Excel tips. Click that "i" in the top right hand corner.
If you like what you see here; Please Subscribe and ring that Bell.
Feel free to post any comments in the comment section below.
Thanks for stopping by.
I'll see you next time for another netcast from MrExcel.
Sum if between 10 and 20.
Hey, welcome back to MrExcel Netcast. I'm Bill Jelen.
Today, a question from John.
John is relatively new to spreadsheets but he had a great question.
Today he's trying to categorize is spending into buckets let's say and John came up with this beautiful formula.
Here he says hey look through the cost column, see if it's less than 10 and then add up a corresponding cost column.
Great use for SUMIF but then next row John asks: Hey how can I make this do greater than 10 and less than 20?
I don't know what version of Excel John is using.
I said, see if you have SUMIFS - plural. And SUMIFS is a little bit backwards. =SUMIFS(.
First thing you have to do is what you want to add up, so that's column B over here.
And then it's pairs of things.
Go look through this column and see if it's equal to this criterion.
Then go look through this column and see if it's this criterion.
Now in this case it should be the same column every single time, so we're going to look through this column, comma.
And see if it's greater than or equal to 10 so >= to 10 like that.
For the second criteria: go look at that same column again.
And see if it's less than.
20.
Alright, and hey, even better than that, SUMIFS can keep going on, so if we needed, let's say we gotta come over here to product and we only wanted to know the photography product, we could say with that column is equal to photography you could have...
I think, 126 pairs of check this column for this criterion.
In this case we just need two like that. Alright, so that gets us the value.
Now the hassle with the way that I built this so far is that we're going to hard code this greater than 10. Less than 20.
I'm going to pause the video for a second.
Alright, so here's a nice flexible formula that does the same thing.
Looked through the cost column, see if the cost column is greater than or equal to, then then ampersand.
That is a concatenation character greater than equal to 10, and then look through the cost column and see if it's less than the next row, which is 20.
And the beautiful thing about this is once we enter it, we just copy it down and we get the answers for everything without having to edit the formula in each cell.
COUNTIFS does what SUMIFS does we're looking for this criteria, spending cost greater than equal to 10, spending costs less than D4. Copy that down.
Hey John, SUMIFS, countifs.
When I was starting with spreadsheets, we didn't have that.
We had SUMIF or COUNTIF there's a lot of different ways to do things in Excel though, and one way that would be a great way to solve this if your spreadsheet has pivot tables, select one cell in your data back here.
Insert pivot table. I'll let it go to a new worksheet.
That's OK, it shows up here on this worksheet and we can build a report here.
Now let's see.
Pivot tables generally start out looking like this, so columns across the top rows down the side.
And this is very unusual.
I'm going to take that cost field and drag it down the side like this and then take the cost field three times to the values area and the first one.
I'm gonna let it be a sum.
For the second one: Now I'm going to double click the heading and say I want to see a count.
Then over here I'm going to double click and I want to see an Average.
Now this report isn't very fascinating 'cause it's looking at every single dollar amount.
Choose one of those dollar amounts. Then group field.
So this is PivotTable, Analyze, Go to Group Field.
Say we want a group, let's say from zero up to 100 based on ten.
Click OK.
Man that is awesome and so for each bucket we know the total cost, how many there were and what the average was.
Right click here for more number format currency 2 decimal places.
I guess makes sense. So pivot table. Great way to go.
Another way, awesome formula here.
This is definitely before we had SUMIFS we would do this take all these amounts over in column B, multiply multiply. This has to be in parentheses.
See if the amount is greater than equal to zero and then multiply. See if the amount is less than 10.
If both of these are true, it's the amount times 1 times 1 and you get the amount.
If either one is false, it gets wiped out to 0, right? So there's lots of different ways to solve this.
SUMIFS, though, great, great, beautiful thing to learn.
Now SUMIFS of course covered in this book, MrExcel LX the Holy Grail of Excel tips. Click that "i" in the top right hand corner.
If you like what you see here; Please Subscribe and ring that Bell.
Feel free to post any comments in the comment section below.
Thanks for stopping by.
I'll see you next time for another netcast from MrExcel.
