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:

Example of a scenario in Excel where SUMIFS will help you add up sales that meet multiple criteria.

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 long-winded 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:
    Excel - a worked example of the SUMIFS function
  • 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.

 

Download worked examples

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.

Add a comment to this lesson

Comments on this lesson

Sales Rep Item Sold

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

Clear and concise

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,

excel function sto solve

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

The SUMIF function should do this for you

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

Different sized ranges are the reason

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

SUMIFS with multiple criteria

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....................

Try a Pivot Table instead

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

Sumifs function

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

using excel as a time sheet

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.

Spreadsheat

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.

More information please?

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

Sumifs adding together all the months of a fiscal year

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

SUMIFS?

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!!

Can you please help, I need

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

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!

Validating across a column and a row?

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

Dear Sir/Madam,

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

Find the SUM of a range of cells containing numbers & text

I am trying to figure out how to find the SUM of a column whose values are auto-generated 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 B1-TODAY().

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,C1-A1&" "&"Days to Edit",IF(C1=0,B1-TODAY(),"")).

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 (B1-TODAY), 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.

rolling sums with a common amount

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. :)