This video covers six topics about conditional sums in Excel.
Table of Contents
(0:00) Topics in this video
(0:33) Conditional Sum in Excel
(1:17) SUMIFS with multiple criteria
(2:09) SUMIFS with 2 criteria in same column
(5:33) How to Drag SUMIFS formula
(6:27) SUMIFS with Function in Criteria
(7:22) SUMIFS with Range of Dates
Table of Contents
(0:00) Topics in this video
(0:33) Conditional Sum in Excel
(1:17) SUMIFS with multiple criteria
(2:09) SUMIFS with 2 criteria in same column
(5:33) How to Drag SUMIFS formula
(6:27) SUMIFS with Function in Criteria
(7:22) SUMIFS with Range of Dates
Transcript of the video:
How to do a SUM with IF conditions in Excel?
There's so many search topics out there trying to do a SUM with an IF.
Or IF with SUM or a conditional SUM.
In this video I'm going to try and address 6 different solutions here.
How to do a conditional SUM in Excel?
How to do the SUMIFS with multiple criteria.
And then multiple criteria in the same column, that's harder.
How to have a SUMIFS formula that you can drag down?
What to do if you have a SUMIFS and the criteria is the result of a function.
And then, finally, a SUMIFS with a range of dates.
So we're going to start off with the easy one.
We're looking for all of the sales where the sales Rep name is Andy.
And we can choose from this list.
So here, we start off with the easy one, the SUMIF.
And the range is the list of names.
We want to look through those for, in this case, Betty. Comma, see if it's equal to Betty, and if it is, then add up the corresponding cell in this range.
Now these A4 to A19 and C4 to C19 have to be the same shape.
If it's 15 rows in the first one, it has to be 15 rows and the second one.
So Betty: 37,130.
If we choose Andy, we have 2753 If we choose Charlie, we have 25,652.
It's harder though, when there's multiple criteria You are not going to use SUMif anymore, you're going to use SUMIFS.
See that extra S makes it plural.
And SUMIFS is a little bit backwards in that we have to specify the numbers first.
Get those out of the way.
Once you've specified the numbers.
Then we say we're going to look through column A and see if it's equal to Andy.
Then we're to look through column B. And see if it's equal to Widget.
Close paren.
If there was a third condition, you could just add comma, the next range, the next condition.
And so on, up to 127 different conditions.
Although I've never seen that many.
So now we have two drop downs.
We can choose Betty and Gadget And see the totals of those.
Ah, OK.
And then it gets harder.
What if there are multiple criteria in the same column?
And there's actually three different ways to solve this.
So the first part of the formula.
Is going to get all of the sales.
Where the product is widget.
And the sales rep is the first name.
Which is Andy.
Right.
So that's the first half.
I'm going to press F2 to edit the formula.
Press Home to move to the beginning of the formula.
The right arrow.
And then Shift+End to select all of the formula except for the equal sign.
I'll do Control C. And then the End key.
Plus and then Control V to paste.
Now in the second one, instead of pointing to Andy.
I want to point to G6 which is Charlie.
Isn't that a horrible formula?
Like, we are actually doing the SUMIFS twice.
So it would be tempting to try and solve this with a FILTER So with the FILTER, I want to get all of the sales.
And then in the FILTER we get to say what to include.
Well, we're going to have to be a little bit tricky here.
I'm going to start with two parentheses.
And the first test is going to be to look through all of the names.
To see if it's equal to Andy.
Close paren.
Plus also get the conditions where the name is equal to Charlie.
So if that's True or that's True.
And then Times.
Look through the products to see if it's widget.
Close paren there.
And what that does is it gives us all the records that are widget records with either Andy or Charlie.
So here's a widget for Charlie, that's the 32.
Here's a widget for Andy, that's the 128.
Of course, we don't want 5 answers there, so we're going to do a SUM around that whole thing.
And we get the same answer of 10912.
Both of those formulas are ugly formulas, and I'm actually going to propose that we go back to a formula from 30 years ago called the DSUM function.
In order for DSUM to work, you have to have a criteria range.
And in that criteria range for the products we always wanted to be widget.
But for the names we would accept either Andy or Charlie.
So check this out.
Equal DSUM.
Specify the entire range including the headings.
That's the database.
Which column do we want to add up?
It's the third column is sales.
And then the criteria range is this range, here.
The DSUM function is actually much shorter than either of the alternatives.
Be sure to check down in the YouTube comments below where people will have other ways to solve this.
OK.
And then a question how to drag the SUMIFS formula in Excel?
So here we want to get all the combinations Andy, Betty, Charlie for gadgets and widgets.
But the formula that was written, and all the formulas written so far have had no dollar signs.
So we have to add dollar signs in.
And my trick for this is just to select the colon and press F4.
So that locks down that range.
Colon and F4, that locks down that range.
Colon and F4.
The F7 and G7 don't get any dollar signs because as we copy it down, we need the Andy to move to Betty.
We need the gadget to move to widget That creates a formula that we should easily be able to drag down and copy.
And when we look down here, we press F2.
You see that we're still looking at the exact same ranges here, but we're looking at Betty and Gadget.
All right, our next question, the 5th question that we are going to try and solve today.
Is how to use functions as the criteria In the SUMIFS?
So, I thought about what criteria would make sense.
Get everything greater than the MEDIAN.
Greater than the AVERAGE.
Less than the MEDIAN, and so on.
So we start off with our SUMIFS formula where we're pointing to the range.
And then the criteria is we're going to look through that same range of sales.
Ah, but then in quotes: greater than Ampersand and then the formula MEDIAN of C5 to C20.
You might sometimes do greater than or equal to like that.
Greater than the average.
So the criteria out here is going to be, In quotes, greater than.
Ampersand AVERAGE of C5 to C20.
Or here would be less than the MEDIAN.
And of course less than It could also be less than or equal to.
Finally, using dates.
So now I have a column of dates.
And this is always one that just confuses me because you don't know how to specify the date.
First we're going to hard code the date.
So we're looking for all of the sales where the dates.
And here in the criteria, I'm going to put, in quotes, is less than 3/31/2025.
And that gets me these items right here 4 + 2 + 1.
If we are looking for between then we're going to use SUMIFS.
Again, specify the range of sales first.
And then we're looking for these dates, comma.
That are greater than 3/31/2025.
And then the next criteria is going to say look through those dates, comma.
And in quotes less than or equal to 6/30/2025.
Close quote, close paren, and now we get everything that's between.
It's great that we can just spell out the date here, and of course I'm in the US where it's month, day, year.
If you're in any other country where it would be day, month, year, you would want to use your local date settings in order for that to work.
It's probably far more likely though, that we're going to have the dates in a start date and an end date like that.
Start off with the SUMIFS.
Specify the range of sales.
And then the criteria range is going to be that.
In quotes greater than.
Ampersand the date cell, comma.
And then the next criteria range is going to be the dates as well, comma.
And then in quotes, Less than, ampersand the end date.
You might need to adjust these be less than or equal to, greater than or equal to.
In my case, because everything is on the first date of the month, it's just going to work.
All right.
Hopefully one of those six topics in this video solves any of these hundreds of search terms.
I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
There's so many search topics out there trying to do a SUM with an IF.
Or IF with SUM or a conditional SUM.
In this video I'm going to try and address 6 different solutions here.
How to do a conditional SUM in Excel?
How to do the SUMIFS with multiple criteria.
And then multiple criteria in the same column, that's harder.
How to have a SUMIFS formula that you can drag down?
What to do if you have a SUMIFS and the criteria is the result of a function.
And then, finally, a SUMIFS with a range of dates.
So we're going to start off with the easy one.
We're looking for all of the sales where the sales Rep name is Andy.
And we can choose from this list.
So here, we start off with the easy one, the SUMIF.
And the range is the list of names.
We want to look through those for, in this case, Betty. Comma, see if it's equal to Betty, and if it is, then add up the corresponding cell in this range.
Now these A4 to A19 and C4 to C19 have to be the same shape.
If it's 15 rows in the first one, it has to be 15 rows and the second one.
So Betty: 37,130.
If we choose Andy, we have 2753 If we choose Charlie, we have 25,652.
It's harder though, when there's multiple criteria You are not going to use SUMif anymore, you're going to use SUMIFS.
See that extra S makes it plural.
And SUMIFS is a little bit backwards in that we have to specify the numbers first.
Get those out of the way.
Once you've specified the numbers.
Then we say we're going to look through column A and see if it's equal to Andy.
Then we're to look through column B. And see if it's equal to Widget.
Close paren.
If there was a third condition, you could just add comma, the next range, the next condition.
And so on, up to 127 different conditions.
Although I've never seen that many.
So now we have two drop downs.
We can choose Betty and Gadget And see the totals of those.
Ah, OK.
And then it gets harder.
What if there are multiple criteria in the same column?
And there's actually three different ways to solve this.
So the first part of the formula.
Is going to get all of the sales.
Where the product is widget.
And the sales rep is the first name.
Which is Andy.
Right.
So that's the first half.
I'm going to press F2 to edit the formula.
Press Home to move to the beginning of the formula.
The right arrow.
And then Shift+End to select all of the formula except for the equal sign.
I'll do Control C. And then the End key.
Plus and then Control V to paste.
Now in the second one, instead of pointing to Andy.
I want to point to G6 which is Charlie.
Isn't that a horrible formula?
Like, we are actually doing the SUMIFS twice.
So it would be tempting to try and solve this with a FILTER So with the FILTER, I want to get all of the sales.
And then in the FILTER we get to say what to include.
Well, we're going to have to be a little bit tricky here.
I'm going to start with two parentheses.
And the first test is going to be to look through all of the names.
To see if it's equal to Andy.
Close paren.
Plus also get the conditions where the name is equal to Charlie.
So if that's True or that's True.
And then Times.
Look through the products to see if it's widget.
Close paren there.
And what that does is it gives us all the records that are widget records with either Andy or Charlie.
So here's a widget for Charlie, that's the 32.
Here's a widget for Andy, that's the 128.
Of course, we don't want 5 answers there, so we're going to do a SUM around that whole thing.
And we get the same answer of 10912.
Both of those formulas are ugly formulas, and I'm actually going to propose that we go back to a formula from 30 years ago called the DSUM function.
In order for DSUM to work, you have to have a criteria range.
And in that criteria range for the products we always wanted to be widget.
But for the names we would accept either Andy or Charlie.
So check this out.
Equal DSUM.
Specify the entire range including the headings.
That's the database.
Which column do we want to add up?
It's the third column is sales.
And then the criteria range is this range, here.
The DSUM function is actually much shorter than either of the alternatives.
Be sure to check down in the YouTube comments below where people will have other ways to solve this.
OK.
And then a question how to drag the SUMIFS formula in Excel?
So here we want to get all the combinations Andy, Betty, Charlie for gadgets and widgets.
But the formula that was written, and all the formulas written so far have had no dollar signs.
So we have to add dollar signs in.
And my trick for this is just to select the colon and press F4.
So that locks down that range.
Colon and F4, that locks down that range.
Colon and F4.
The F7 and G7 don't get any dollar signs because as we copy it down, we need the Andy to move to Betty.
We need the gadget to move to widget That creates a formula that we should easily be able to drag down and copy.
And when we look down here, we press F2.
You see that we're still looking at the exact same ranges here, but we're looking at Betty and Gadget.
All right, our next question, the 5th question that we are going to try and solve today.
Is how to use functions as the criteria In the SUMIFS?
So, I thought about what criteria would make sense.
Get everything greater than the MEDIAN.
Greater than the AVERAGE.
Less than the MEDIAN, and so on.
So we start off with our SUMIFS formula where we're pointing to the range.
And then the criteria is we're going to look through that same range of sales.
Ah, but then in quotes: greater than Ampersand and then the formula MEDIAN of C5 to C20.
You might sometimes do greater than or equal to like that.
Greater than the average.
So the criteria out here is going to be, In quotes, greater than.
Ampersand AVERAGE of C5 to C20.
Or here would be less than the MEDIAN.
And of course less than It could also be less than or equal to.
Finally, using dates.
So now I have a column of dates.
And this is always one that just confuses me because you don't know how to specify the date.
First we're going to hard code the date.
So we're looking for all of the sales where the dates.
And here in the criteria, I'm going to put, in quotes, is less than 3/31/2025.
And that gets me these items right here 4 + 2 + 1.
If we are looking for between then we're going to use SUMIFS.
Again, specify the range of sales first.
And then we're looking for these dates, comma.
That are greater than 3/31/2025.
And then the next criteria is going to say look through those dates, comma.
And in quotes less than or equal to 6/30/2025.
Close quote, close paren, and now we get everything that's between.
It's great that we can just spell out the date here, and of course I'm in the US where it's month, day, year.
If you're in any other country where it would be day, month, year, you would want to use your local date settings in order for that to work.
It's probably far more likely though, that we're going to have the dates in a start date and an end date like that.
Start off with the SUMIFS.
Specify the range of sales.
And then the criteria range is going to be that.
In quotes greater than.
Ampersand the date cell, comma.
And then the next criteria range is going to be the dates as well, comma.
And then in quotes, Less than, ampersand the end date.
You might need to adjust these be less than or equal to, greater than or equal to.
In my case, because everything is on the first date of the month, it's just going to work.
All right.
Hopefully one of those six topics in this video solves any of these hundreds of search terms.
I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
