You are here
Use SUMIFS to sum cells that match multiple criteria in Excel
The SUMIFS function in Excel allows you to sum the values in a range of cells that meet multiple criteria. For example, you might use the SUMIFS function in a sales spreadsheet to to add up the value of sales of a specified product by a given sales person (e.g. the value of all sales of a microwave oven made by John). This lesson shows you how to use SUMIFS in your own spreadsheets.
Note that SUMIFS was introduced in Excel 2007. This lesson therefore only applies to Excel 2007, 2010, 2011 and 2013.
Also, you'll find a number of examples in the comments at the end of the lessons where people have been trying to use SUMIFS when a Pivot Table is a better solution. If you can't find what you want here, try our lessons on Pivot Tables instead (opens in a new tab).
Using SUMIFS  an example
Consider the following table that contains sales data:
Imagine you want to add up the sales amounts that are greater than $500, and where the quantity sold was 3 or more. You could solve the problem by creating an additional column and use the IF function to evaluate the sales amount and the sales quantity and calculate whether or not to include that sales amount in the total, but that would be somewhat longwinded AND would mean you have to have another column in the table. But the SUMIFS function means you don't have to.
The SUMIFS() function syntax
The SUMIFS() function has the following syntax:
=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2,....)
The function arguments are as follows:
 sum_range is the range of cells to be added up
 criteria_range1 is the first range of cells that are to be checked for a criteria match
 criteria1 is the criteria against which criteria_range1 is to be checked.
 criteria_range2 is the second range of cells that are to be checked for a criteria match
 criteria2 is the criteria against which criteria_range2 is to be checked.
You must have at least one criteria range and criteria. However, if you only have one criteria range, then you may as well use the SUMIF() function instead (it works in almost exactly the same way as the SUMIFS function).
SUMIFS lets you have up to 127 different sets of criteria_range and criteria. Note that the shape of each criteria_range must be the same as the shape of the sum_range. This isn't necessary with the SUMIF function.
The SUMIFS() function in action
Let's use the SUMIFS function to solve the scenario introduced at the start of this lesson.

The table above now looks like this (I've included the SUM function so you can see the overall sales total for the table:
 As you'll see, B41 is showing the total sales, but only for those days where the quantity sold was greater than 3 (criteria1) and the sales amount was greater than $500 (criteria2). The two rows that meet both of these criteria in the table above are Thursday and Friday. The SUMIFS formula I used to perform this calculation is shown in cell C41 so you can see how it works.
 Remember that, as with the SUMIF function, when you're using a criteria such as "greater than", you need to use quotation marks around the criteria, i.e. ">3" and ">500".
 The same applies if you wanted to use a text criteria, such as the day of the week. An example of a text criteria might be "Thursday" if you only wanted sales made on Thursday (useful if you have a spreadsheet like this that spans several weeks).
I've included a downloadable copy of the spreadsheet above, which also includes the SUMIF examples based on the same sales table. You can use this to try out different variations on the example above. Try changing the values in the Quantity and the Sales Amount columns, and check that the formula works correctly. Then, as a stretch, try adding a third criteria range to the formula so that it only adds sales for Thursday.
Did this lesson help you understand the SUMIFS() function? Do you have an alternative scenario where you've used the SUMIFS function? Tell us about it by writing a comment below.
Want to learn more? Try these lessons:
Our Comment Policy.
We welcome your comments and questions about this lesson. We don't welcome spam. Our readers get a lot of value out of the comments and answers on our lessons and spam hurts that experience. Our spam filter is pretty good at stopping bots from posting spam, and our admins are quick to delete spam that does get through. We know that bots don't read messages like this, but there are people out there who manually post spam. I repeat  we delete all spam, and if we see repeated posts from a given IP address, we'll block the IP address. So don't waste your time, or ours.
Comments on this lesson
Sales Rep Item Sold Units
John Fridge 4
Balu Washing Machine 5
Muneer Microven 6
John Washing Machine 7
Balu Microven 8 Data
Muneer Microven 2
John Washing Machine 3
Balu Washing Machine 4
Muneer Fridge 3
Sales Rep Fridge Washing Machine Microven
John 4 10 o
Balu 0 9 8 Sample
Muneer 3 0 8
Sales Rep Fridge Washing Machine Microven
John Report
Balu
Muneer
Please solve this problem
abdul ali
You could solve this problem using SUMIF and SUMIFS, but I would recommend you use a PivotTable instead. You can learn how to use a PivotTable in this lesson. It's a lot more flexible and powerful and will do exactly what you need.
Column A=Sales Rep, Column B=Items, Column C=Sold Qty
I need individual record (each Sales Rep, each item ,total sold qty)
Great tutorial on the use of SUMIFS . Excel is really a powerful tool and it is very helpful when one can get what they need online in the way of help. Most books that cover these topics (yes , there are still books) go into too much over the top things and don't get into the real meat of the subject  like this does, covering what I need to know ! Thank you,
Can any one please help me to use the similar formula in excel to find out
the sum the Row 3 IF row 1 is equal to "SecSch" AND row 2 is equal to "Current"
Say in excel Row 1 (contains a list of "Sec", "Pry", "RCs"), Row 2 (contains "Current" and "Capital"), and Row 3 (contains list of budget figures 135.45)
Thank you in advance
I have a column with with categories and next to that are their numbers/amounts. I want to add/sum certain categories together? I have the categories identified by text ...thanks
Hi E Dot
Sounds like you need the ordinary SUMIF function rather than the SUMIFS function. This will allow you to add up numbers that match the categories you choose. If you need to add up more than one category in a single formula, simply put multiple SUMIF functions together in the same formula. In this scenario each SUMIF function would add up all the values for one category.
Check out the SUMIF lesson here.
Regards
David
I beleive I understand the material, but cannot undertsand why this produces a #VALUE! error.
=SUMIFS($E$2:$E$51,$F$2:$F$51,$D81,$G$74:$G$84,$E$74)
Hi Tom
I think your problem is that the first sum range and the second are different sizes. If you change $G$74:$G$84 to $G$2:$G$51 then the formula no longer generates an error.
Given your example, I would create a formula that uses the SUMIF function twice rather than trying to use SUMIFS, e.g.
=SUMIF($E$2:$E$51,$F$2:$F$51,$D81)+SUMIF($G$74:$G$84,$E$74)
Regards
David
Hi David,
Thank you very much for the reply. I didn't know about using two SUMIFs.
I really appreciate your help. I'm pretty good at using arrays to explore massive amounts of data at Intel, but really ignorant of the Excel syntax to do other things.
If you have the time and interest, I try to explain better. If you don't, Thanks again.
I'm not sure if were able to look at the file and see what/where the ranges are.The error is gone, but I'm not getting the correct answer. (I didn't explain what I was trying to do, so it wasn't very clear.) After two hours of trial and error, I was crabby and desparate.
I'm trying to sum the amounts in col E by month (Col F) and by catagory 1 or 2 (Col G). Col E is month # reference and D81
is the target Category number. I'm sure there are better ways to do it, but I only know what I've figured out on my own.
The results are reported farther down the sheet.
The first part worked fine in summing by month, but I wanted to separate Totals by Catagory 1 and two. The correct answers for Month 8 are $355.00 for Cat 1 and $278.87 for Cat 2. The formula as is produces $8 and $38, so something isn't playing right. I've tried using * and + to combine the formula sections, but that doesn't work either.
Thanks again for the tip about separate SUMIFs. I need to take a class....................
Hi Tom
Looking at your spreadsheet, I'd say that you could do this with formulas but a Pivot Table will summarise your data far more quickly and easily. Why don't you check out our lessons on Pivot Tables and see if you can find a solution there. If you can't, post a comment again and I'll see what we can do to solve the problem for you.
Our Pivot Table lessons are summarized here.
Regards
David
Thanks, David. I haven't used Pivot tables for quite a while, so I'll take your advice and do the class.
Thank you for taking the time!
I appreciate it.
Take care,
Tom
In your example, if I wanted to sum the sales for Monday, Wednesday and Friday, would I need three criteria and repeat the crteria range for each criterion? Thanks
I have different levels of PTO for my employees, some get paid holidays at 7 hours, 7.5 hours, 4.5 hours and 3 hours. It is a challenge to remember who gets paid what; I am not the book keeper, I just do the time sheets for the book keeper. I would like to use a function that would automatically return the specific rate for each person. I use an excel time sheet that automatically adds up hours for each pay period, I was just hoping to simplify even more. Any help would be appreciated!
Example: if Sally gets PTO for the 4th of July, I would like to place an "X" in the cell (labeled PTO) and have it automatically give her 4.5 hours of pay; and Fred will automatically get 7.5 hours PTO when I place an X in the 4th of July cell.
This way I only have to "x" the holiday or vacation day, not remember how many hours each of them are compensated.
If you were to add another column that said what they got paid for PTO. then you could do a sumif statement that would check that column when you add an X to all of the spots. So if x was the input for the fourth of july, it could check whatever column the variable for PTO is in, and assign a amount based on that. Im not sure what the syntax line would look like, but that is one way to solve the problem.
Hi Laurie
You're on the right track but it's a bit hard to figure out how you've structured your spreadsheet. Is it set out as a timesheet, i.e. each person gets a row for each day they work, which has a column for the date and the hours worked? That's how I'd set out a timesheet in Excel, but it sounds like you have something different? If I know more about the way your data is structured, it will be easier to provide a definite solution for you.
Regards
David
I need to sum all of the sales to date in a fiscal year. I have all the criteria set except I can only get it to pull one months data. =SUMIFS(Data!$H:$H,Data!$C:$C,$AZ$1,Data!$B:$B,Bacardi!$A$4,Data!$A:$A,Bacardi!$D5,Data!$E:$E,Bacardi!$A$16) this is the formula.
Data!$C:$C,$AZ$1 is the range and criteria that pulls the date
I need a forumla to add all values up for a customer, but only when this has been invoiced
In column I I have the order values
In column J I have the customer
In column S I have the invoice number
So, unless there is a value in column S, I do not want it to be in the figures, but each customer needs to have a total figure. There is probably an easy solution, and I am trying to overcomplicate it with trying to make it work with SUMIFS!!
Hello,
I need to sumif for a given range of variables. for eg, in the attached sheet I want sum of revenue of 100 odd batches. There are around 1000's of batches in total. Could you pls help.
Can you please help, I need to total the contents of D,E,F & G in column C  but only having one total in column C per person  i.e. for Lenon, James I would expect to see blank cells in C2 and C3 and the total 17 in cell C4  this has been driving me insane!
Can you please help, I need to total the contents of D,E,F & G in column C  but only having one total in column C per person  i.e. for Lenon, James I would expect to see blank cells in C2 and C3 and the total 17 in cell C4  this has been driving me insane!
Hello,
I found you articles very useful recently when tasked with an excel project at work. I had a bit of experience, but having to combining IF and SUMIFS formulas etc. in one was a new level for me.
One thing I tried after looking into you articles, and the very useful solutions in your comments was to try and calculate the total of a number of cells after validating the criteria against a Column and a Row. The SUM area then became more of a grid then a column. I've provided an example formula below:
=SUMIFS(B2:Z80,A2:A80,"=C*",B1:Z1,"=Bell")
this resulted in a #VALUE error.
is there a different, or combination, of formulas that would allow me to do such analysis?
Thanks
Mark
my SUMIFS() function fails to consider <= or <> conditions. How can i do it??
I'm trying to sum up my hours based on client and task. How can I do that?
Dear Sir/Madam,
I have a problem to add three cells having different conditions. For example a student get 78 marks out of 100 in paper A, 60 marks out of 75 in paper B and 14 marks out of 50 in paper C. Now I want to add these marks only if the paper is passed if not then those marks would not be included to the total sum using different condition for each one. Like, " add if 100>33, 75>25 and 50>17. How should I do it. Please guide me
I am trying to figure out how to find the SUM of a column whose values are autogenerated by a different function. The column values are determined by an IF function based on date values. The date values are Review Date(A:A), Deadline Date(B:B), 2nd Review Date(C:C), and the column (D:D) whose values I'm looking to SUM operates as follows:
If A1 and B1 have date values, and C1 is blank, then D1 will result in the number of days remaining based on B1TODAY().
If A1 and B1 have date values, and C1 also contains a date value, D1 will result in the number of days from the date in A1 to the date in C1, and will be followed by the text string &" "&"Days to Edit".
A simplified example of the formula I'm using for D1 is: =IF(C1>0,C1A1&" "&"Days to Edit",IF(C1=0,B1TODAY(),"")).
I'm assuming that the "Days to Edit" text string is preventing me from being able to SUM the numeric values that precede the "Days to Edit" text string, because when C1 is blank and D1 just returns a numeric value (B1TODAY), I can highlight the cells and select the "SUM" from the drop down menu at the bottom of the Excel window and get the total without any problems. However, when I just select cells that contain a number followed by "Days to Edit", the SUM = 0.
Is it possible to just calculate the numeric values that precede the text string, or do I have to remove the text string?
Also, the function column (D:D) is formatted as a number value.
I'd appreciate any guidance on this issue as soon as possible.
I need to figure out how to do a sum if for every Â£1000 sold they would earn Â£4.50.
It needs to be a rolling total in a cell.
I have set up already the sums for the revenue input. ie  =sum(a2:a30) so this calculates the total revenue in a31 cell. But then i need cell a32 to give me the earnings for the staff as mentioned above. :)
Just what I was trying to do and really easy to understand. Solved my problem quickly, thanks so much.
Column "A" is the social security number of a debtor. Column "B" is an account number. And finally Column "C" is the amount due on that account. A debtor with the social security number in Column A could have multiple rows of accounts and totals (Column B & C). I need to sum column "C" whenever the value in Column A changes. So for each social security number in column A, I need to have only one total of all the account totals in column C. How can I easily get this done? THANKS!!!