In Excel 2007 and Excel 2010, you can use the new SUMIFS function to sum items using multiple criteria.
For example, sum the orders where an account status is Active, and the number of visits is greater than or equal to 10.
You can type in the criteria, but it’s better to use cell references, where possible.
I’ve updated the Excel SUM page on the Contextures website, to include the SUMIFS function example and video.
Watch the SUMIFS Video
To see the steps for creating a SUMIFS formula, and using cell reference, watch this short Excel video.
There is a full transcript below the video
Video Transcript
In Excel, you can use the SUMIFS function if you’d like to sum a column based on values in two or more of the adjacent columns.
Here we would like to find a total where the status is Active, and the number of visits is greater than 10.
This only works in Excel 2007 and later versions.
In this cell we’re going to create a formula, starting with an equal sign, and then SUMIFS, open bracket.
Then the first thing we want to do is indicate which cells will be summed once we set the criteria. The amounts are what I want to sum in C2 to C6
Then a comma, and we’ll do our first criteria range. So where should it check for the first criteria?
That’s going to be the status. I’ll select those cells, type a comma, and I’m going to type in the status that I’m looking for.
So inside a double quote, I’ll type Active and end with another double quote and then a comma.
The second criteria range is the number of visits. I’ll select that, and then a comma.
This time I’m going to use operators, as well as the number 10. Within quotes, greater than, equal to, and another quote. We’re going to find visits where it’s greater than or equal to, and an ampersand, and the number 10
Then close the bracket, and press Enter. We can see there were a total of $325 for Active accounts with 10 or more visits.
Instead of typing our values into this formula, we could use a cell reference.
Up here, I have a cell that says Active, and one that has our minimum value that we want to check for.
I’m going to select the double quotes and the word Active and just click on the cell that has that criterion.
The same thing where I have the 10 here. I’m going to click on the cell where I’ve typed a 10. When I press Enter, we get the same number as our results, but now it’s more flexible.
I can type 5 here instead of 10, and we might see a different result here. I can see there’s one there a 4, so if I type a 4, my value changes to include that $100 amount.
_____________
I am always amazed that no one ever uses the data functions of excel (any version)
Table name “DATA” = A1.C6
Criteria =
STATUS=”Active” Visits >=10(place in E1.F2)
=DSUM(data,3,E1:F2) = 325
Great blog.
On this subject, I believe in some builds of Excel 2010, there is a bug in the SUMIFS formula, whereby if your criteria arrays are spread over different sheets, the formula may evaluate to zero or indeed to just a plain wrong answer.
I think (not sure) – that this issue has been fixed by an update or service pack from Microsoft, but for users who have not had it applied they should be aware….
Hi Stuart:
Not sure if my issue is related to this bug but here’s my formula and excel is not returning any data. I know that there are records on the file that have amounts > 100K.(in the H cells) Maybe you can figure out if I have something wrong? Thanks a lot, Charlie
=SUMIFS(Data!$H$3:$H$34710,Data!$H$3:$H$34710, “>100000″,Data!$AB$3:$AB$34710,Summary!E$3,Data!$AA$3:$AA$34710,Summary!$B$68,Data!$AC$3:$AC$34710,Summary!$C69,Data!$U$3:$U$34710,”*intergov*”)
1 1/29/2012 2/25/2012
Date Quantity Price
1/29/2012 0 4Â
1/30/2012 2 7Â
1/31/2012 1 9Â
2/1/2012 1 16Â
So the the number “1” is a drop down (1 to 12) , when I change the from “1” to “2”, the starting and ending date changes, so if I select 2, the starting date would changed from 1/29/12 to 2/26/12, and ending date will change from 2/25/12 to 3/31/12, the dates are +1 from starting date, the quantity and price are vlookup based on date, there are 35 set lines (coz that is the max it can have, but I want only the ones to sum with the start and end date. So say even if it there is something on 2/26, when I have 1 selected I want it to sum only till 2/25 based on the last date.
=SUMIFS($D$2:$D$605,$A$2:$A$605,”>=”&$F660,$A$2:$A$605,”<="&G660,$B$2:$B$605,OR(C660,K660))
This formula worked until I tried to do an OR at the end to include K660. Do I just have bad syntax or can it not be done?
Can the same criteria range be used more than once? All the examples I have seen refer to two separate criteria ranges such as “Status” and “Visits” columns your example shows.
I have two columns (sum range = D3:D17,and criteria range = C3:C17). The first formula listed returns 0 as the value. Intent is to add the values in Column D if Column C is equal to a 20 or a 50.
=SUMIFS(D3:D17,C3:C17,20,C3:C17,50)
If I eliminate the second criteria, the formula works and returns the sum of the items with 20 in the criteria range; but isn’t any improvement over SUMIF.
=SUMIFS(D3:D17,C3:C17,20)
If I add two SUMIF formulas together, I get the correct value.
=SUMIF(C3:C17,20,D3:D17)+SUMIF(C3:C17,50,D3:D17)
Any clue why the first wouldn’t work? I’m using Excel 2007.
Ron,
The first one will not work as it is looking at two criteria to be true, and no cell in C3:C17 can be two values at the same time !
Peter
Why Does Sumifs work with operators of Less Than or Greater than, but not exact numbers? Example
=SUMIFS(D3:D17,C3:C17,20,C3:C17,50)
This will not work, but
=SUMIFS(D3:D17,C3:C17,”>” & 20,C3:C17,”<" & 50)
This will work.
Juanita,
If you use “<50" (or whatever the number you are testing against) in the SUMIFS, I have gotten that to work. I don't know what has been changed, but I seem to get sporadic results using a list {value1, vaue2, …} in hte criteria.
@Juanita,
SUMIFS works for exact numbers:
=SUMIFS(D3:D17,C3:C17,”=20″,D3:D17,”=40″)
Your first example works because it is summing values in D where column C contains a value between 20 and 30 (>20 AND <50). Your second example is summing values where Column C contains 20 AND 50. As Pete says above, this can never happen.
Approved Formulas:
=SUMIFS(H2:H17,A2:A17, “>=19999”,A2:A17, “<=30000")
=SUMIFS(Sheet1!H2:Sheet1!H17,Sheet1!A2:Sheet1!A17, A2)
Optional Formulas:
=VLOOKUP(A2,Sheet1!$I:$J,2,FALSE)
=SUMIFS(H2:H17,A2:A17, "a")
good one, it helped me a lot
I’m trying to use SUMIF formula with two different sheets (both are in same file) however I keep getting zero as answer. HELP! Example =sumif(‘filename’!$D$2:$D$100,A10,$G$4:$G$100) is there something that I’m missing so formula will get data from other sheet? THANKS!
Can you use sumifs when your criteria is or, vs. and? If so how should this be written?
@Cindy, you could use 2 SUMIFS, such as:
=SUMIFS(C2:C6,A2:A6,”Active”,B2:B6,”>=10″) + SUMIFS(C2:C6,A2:A6,”Hold”,B2:B6,”>=10″)
This returns a value:
=SUMIFS(‘Program ‘!$X:$X,’Program ‘!$V:$V,”=” & F3)
But this does not return a value, only 0
=SUMIFS(‘Program ‘!$X:$X,’Program ‘!$V:$V,”>=” & F3)
Any idea why?
Did this ever get resolved? I’m dealing with the same issue.
Hi,
I am using =SUMIFS(Q15:Q155,P15:P155,”2014″,O15:O155,”Canada”), but it is returning “0” please healp me to get expected result.
@Avik, try 2014 instead of “2014”