Excel SUMIF and SUMIFS Formulas Explained

In this tutorial we’re going to explain how to use Excel SUMIF and SUMIFS Functions, and look at a couple of different applications for them.

You can download the Excel workbook used in this example and practice what you learn (you'll find the link to the workbook below).

Learning SUMIF extends the capabilities of the basic SUM function by allowing you to tell Excel to only SUM items that meet a certain criteria. Whilst the SUMIFS function, new in Excel 2007, allows you to stipulate multiple criteria, hence the plural.

Enough explanation, let’s dive into an example as it’s easier to visualise.

Excel SUMIF Function

The function wizard in Excel describes the SUMIF Function as:

=SUMIF(range,criteria,sum_range)

Not very helpful is it? Let’s translate it into English with an example. In the table below we want to sum the total number of Units (in column D) for Dave:

Translated, our formula would read like this:

=SUMIF(the name in column C, = Dave, add the figures in column D)

We could even put a summary table at the bottom of the list for each builder like this:

Our formula in cell D12 would be:

=SUMIF(C2:C7,"Dave",D2:D7)

While the above formula is good, if we were to copy it to the rest of the summary table (cells D11 to D14 and F11 to F14), we would have to manually change the cell references and builder’s name to get the correct answers.

The solution is to use absolute references to help speed up the process of copying the formula to the remainder of column D and column F. With absolute references our formula would look like this:

=SUMIF($C$2:$C$7,$C12,D$2:D$7)

We could then copy and paste the formula to the remaining cells in our summary table without having to modify it at all. As we copied it down column D, Excel would dynamically update it to automatically pick up the next builder in the list. Then when we copied it across to column F, Excel would dynamically alter the reference to column D to F.

The best way to fully understand this conundrum is to try it for yourself.

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.

I was assigned in tax department.but I am did not study an accounting course.my boss give me an assignment that I have to do alphalist for the vat returns.the problem is I was using SUMIFS but I cannot get the exact total I needed.is there any formula to solve it?

HI, THIS HELPS ME A LOT, I HAVE EXCEL SHEET OF STUDENTS SCORES WITH 15 SUBJECTS HISTORY GEOGRAPHY MATHS PHYS CHEMIST BIOS ENGLISH FRENCH COMMERCE BOOK KEEPING ETC,GIVEN CRITERIA A=1,B=2,C=3,D=4 AND F=5, I WANT TO SUM UP THE BEST 7 SUBJECTS ONLY BASED ON CRITERIA DIVISION 1 FROM 1 TO 17, DIVISION 2 FROM 18 TO 21, DIVISION 3 FROM 22 TO 24 DIVISION 4 FROM 25 TO 28 DIVISION 0 THE REST, PLEASE HELP ME THE FORMULA

Hi Veitinia,
Please prepare a sample file with detailed instructions on what you are trying to achieve, I will gladly help you.
You can upload the file on our Help Desk (create a new ticket).
Cheers,
Catalin

I am currently using this formula in my excel to calculate the number of people per line of business are on this spreadseet using an array formula
=SUM(IF(F13:F85=”NY MEMBER”,1,0))
I have column C that has three text options: Late, Call OFF and NCNS.
How do I configure the calculation to calculate the number of calloffs per that line of business only? I hope I explained that right for you.
I am trying to calculate how many calloffs per line of business by excluding the ncns and lates.
I hope that you can help . Thank You!

Organization refuses to create a category and allows users to free form entry of school names. Trying to create a SUMIF to cover an array of variations of the name of the school. The SUMIF works with only one “wildcard” text name. For instance, school called “Northwest Elementary,” so I entered “*Northwest*” to include any mention of the school name. But some users abbreviated it “NWES.” How can I include a named range (my array) or multiple wildcard variations in the SUMIF formula?
SUMIFS seems to cover only AND conditions and not OR.
Would appreciate any guidance you can give me. And I VOLUNTEER for this as community service for my Master Gardener certification.

Hi Donna,
You can try a minimum search string that covers all situations, like: “*N*Wes*”, or just “*N*W*”
If this is not working, upload a sample file at our Help Desk, for a personalized solution.
Catalin

Hi George,
Using a pivot table to manipulate large data sets is a good idea. You can use value filters on sales column to get the desired results. From your message, it’s hard to understand what you need, maybe you can give us more details and show us where you need help in this process.
Cheers,
Catalin

Hi Bill, you can use UDF’s, as this is not possible with regular excel functions. You can find from the web the list of excel color index, or you can use the function CellColorIndex to find the index color of your range. Then you can use that index in SumIfByColor function.Function CellColorIndex(InRange As Range, Optional _
OfText As Boolean = False) As Integer

Hi Lizz,
The formula should look like: =SUMIF($D$2:$D$7,H$5)
First argument is the criteria range, second argument is the criteria to be searched in the criteria range. The third argument is optional, and must be used only if the range to sum is different than the criteria range. As an example, if you want to sum cells from range E2:E7, but the criteria is on D2:D7, the formula will be: =SUMIF($D$2:$D$7,H$5,E2:E7)
Catalin

It would be better if you’d state what type of math
you are using here. To be honest, we are not
statisticians/engineers/mathematicians so we
will know what exact built-in function is equivalent to this
computation of yours.

Great website! I am trying to apply your SUMIF formula to my own spreadsheet, but having trouble figuring out how to make this work because my range is a list of DATES- (formatted as 1/1/13).

Can you tell me what your formula would look like if you were sorting by date (column A) in your first example above instead of sorting by name (in your example- “Dave”). In other words, if you were looking to find out how many units were sold in a given MONTH, rather than by a given BUILDER.

Hi Ashish,
SUMIFS function does work with ranges with multiple rows and columns, as long as all the ranges do have the same size:
=SUMIFS(A1:E4,A1:E4,”>3″,A1:E4,”<6")
You can try the SUMPRODUCT version:
=SUMPRODUCT((A1:E4>3)*(A1:E4<6)*A1:E4)
You can add as many criterias you need, I used only 2 criterias in this example.
Catalin

I have a table where I want to use a sumif where it checks a PO has been raised for the month of January and give me a value. Now the month of January is in a date which has to be converted to a month. i.e. 24/01/2013 -> January. E.G.

In a nutshell, to create a table, simply highlight the range (including headers) of your data.
Then, go to insert ribbon and click 'Table'. Once you have the table you can now reference them
like I did with the formula above.

Apologies, the example translated poorly in this text box.
it was a list of values for each fruit, sum for each and a sample of each formula type trying to exclude one of the reference columns.
D / E
Apple /3
Orange/8
Pear/1
Apple /5
Orange/2
Pear/6
Apple/9
Orange/7
Pear/4

I was trying to exclude Apple in the formula as either a referenced cell or range name. The only correct answer (28) was when the text value was included in the formula.

I can’t seem to get any SUMIF functions to work. I have excel 2007 and I downloaded your Excel_Blog_Workbooks file, however When I click on a SUMIF cell and then either click off it or try and press enter, I get a “The Formula you typed contains an error” even though I never changed anything and the formula in theory should work.

I tried to download the file and nothing is wrong even as I simulated the actions
you did that created or triggered the error.

I suggest it is about your Excel’s regional settings or something.
I had this German project before and there are some differences like
We used dots/periods for decimals while they use comma’s for it.
In formulas, we use comma’s to separate arguments while they use
semi-colons. So I’m just saying maybe it has some relevance to your
problem.

So I suggest this:

Change the country or region setting
The country or region setting in Windows, referred to as location, indicates the country or region that you are in. Some software programs and services provide you with local information, such as news and weather, based on this setting.
Open Regional and Language Options by clicking the Start button Picture of the Start button, clicking Control Panel, clicking Clock, Language, and Region, and then clicking Regional and Language Options.
Click the Location tab, select your location from the list, and then click OK.

Using the SUMIF formula I am summing amounts on a different tab in the same workbook as my unique identifier (in this case, employee ID). If I were to sort the rows on the tab where the formula is entered, using the filter button, my SUMIF formula doesn’t completely update. It is almost as if the formula contained an absolute reference ($’s).

The “‘Expected Payouts’!F85” portion of the formula is the portion that doesn’t get updated. This formula is on the “‘Expected Payouts'” tab and the information I am trying to sum is on the “Payroll Detail” tab. So what happens is when I sort my information on the Expected Payouts tab (which does include the column where the formula is entered), this particular line was on F85 but now is located on F10 however the formula does not update to F10 like a relative reference formula would. Is this just a constraint of this formula or is there something I am doing wrong?

I appreciate any assistance you can provide. This website has been most helpful so far.

What you should do is simply lose the dollar($) signs. So what you should do is maintain
the absolute references in your SUM range and Range and change your Criteria range from $A$2 to
simply A2 –just an illustration. Do this to your first formula and drag it down as applicable.

Hi – great site… I’m trying to use the SUMIF function using a formula as condition, but doesnt work…
SUMIF(A1:A5;”<$B$7;C1:C5)… The problem is that exceld oesnt recognize B7 as an active cell but as text.

SUMIF/S won’t work in this case because you need the criteria on the row you want to sum, not before/after. For example; row 17 has an S in column A but 1 in column C, so if I understand you correctly you wouldn’t want to sum rows 18-21.

I’m not sure how you can go about this. You ideally need an identifier on each row that classifies the row as something you want to add or want to exclude. Is there any other data you could use to identify the rows you want to include?

Morning Mynda.
Thanks for you reply.
I have uploaded an image file to http://dl.dropbox.com/u/4688931/excel%20grab.gif
The yellow lines are the ones I need to populate with results. As you can see the number of rows between each yellow line varies. E3:E9 needs to be in E2, I3:I9 needs to be in I2, then down a yellow line and I need E11:E17 needs to be in E10, I11:I17 needs to be in I10 ……..
“Cost”, “TotalMaterialCost”, “LabourTotalCost”, “LabourTotalTime” columns needs to sum the values between the yellow lines. E2, I2, K2, M2. Then E10, I10, K10, M10 …….. on and on.
I thought =SUMIFS using the “S” in column A and the “0” in column N would work, but each way I try I’m wrong

I could probably come up with a complicated formula using OFFSET to do this for you but fundamentally the way you have the data laid out isn’t ideal.

What you have here is a combination of a report and a database which makes your data incredibly inflexible for analysis purposes, and application of formulas like SUMIFS.

Ideally you would have 1 more column to house the Dxxxx which would mean you could then use some of the built in tools like Subtotals or PivotTables to automatically summarize the data for you.

e.g. Rows 3:9 would have a D10102 in column A, rows 11:17 would have a D10103 in column A and so on. You would get rid of rows containing S as Subtotals or PivotTables will automatically put these in for you.

I can’t change the file for you, you need to do that…especially since you only sent me an image. Once that is done you can read through the tutorial for Subtotals or PivotTables and generate the report your need using either of those tools.

Hi,
Can you please help me with the SUMIFS example you explain (shown below) HOw would I add a second criteria to my total eg. I want a total units of both Doug and Brian under Central as one total. Not just Doug. I have tried everything but am now lost.
Your formula to get Doug’s total below reads:
=SUMIFS(D$2:D$7,$B$2:$B$7,$B$17,$C$2:$C$7,$C18)

To sum Doug and Brian requires an OR argument, however SUMIFS can only handle AND arguments.

For example SUMIFS formulas read with AND between each criteria. SUM Units where the region = central and the builder = Doug OR Brian. It can’t handle the OR which means you can only choose one criteria from each column.

Having trouble with this: I have a list of dates in one column, profit and loss in another column. I want to be able to keep running totals for each month on another column. do I set the dates and P/L columns in an array and extract the dates or I was using sumif but I couldn’t figure out how the keep a total in the month column being some months will have different numbers of dates.(I.E. Jan might have one transaction, Feb might have 10, March might have 5). I am having trouble with the date ranges in the dates columns \.

Well, I am running out of words to admire the way you explain everything about excel….it is right to say that I am excelling in excel 🙂
But, I am at a point that I need extra help from you. I need more questions to practice on every condition, formula, etc., in excel. I googled for it, but no luck. I hope my search ends here! I appreciate your response.

Thanks for your kind words. I find a great place to get practice is by helping answer questions in forums. You often find challenges you wouldn’t have thought of, and as other members of the forum answer the question you are likely to learn a few different ways to solve the problem.

Good evening, this is the work you do, you have given me much help. I thank you already.
My problem with the function sumifs is that I would add the cells found in SEVERAL worksheets with four conditions.
help me stp. thank you in advance

This is a great help.
How can i bring this further by summing if using a word that is only part of a longer word.
For example I want to sumif the word “Apple” is in the cell.
Possible entries are: Green Apple, Red Apple, Granny Smith Apple.
Thanks.

Thanks for your site. I am trying to figure out how to use SUMIF, and I thought that you might have solved the problem, but then I ran into a dilemma. You state that you can use the formula:
=SUMIF(A1:A7,$H$7,B1:B7)
AND
You can use the formula
=SUMIF(A1:A7,”>5″,B1:B7)
So, why can’t I use something of the form:
=SUMIF(A1:A7,>=$H$7,B1:B7)
OR
=SUMIF(A1:A7,”>$H$7″,B1:B7)

The first two give me an answer, but the last one bombs out.

I am trying to write a formula that would scan a column for a date that is less than a particular date. And then I want to sum all of the dollar amounts associated with the dates that are less than or equal to that date.

Odd, it looks like my comment got garbled. I had written more than that. What I was trying to convey is this.

The SUMIFS formula works quite well when you define the criteria specifically in the formula, but not so well when you tie the criteria to a cell reference. For example, if were to add a date of 2/1/2008 in cell G1 and desire to sum all of the units sold after that date, I think the formula would look like this:

=SUMIFS(D2:D7,A2:A7,>=G1)

I get a result of 0 when I input that formula, however. If I put the actual number representing 2/1/2008 in the formula, it works. The Excel number for that date is 39479. That forumula would look like this:

=SUMIFS(D2:D7,A2:A7,>=39479)

It returns the correct value of 36. I didn’t follow your thought about using the & reference. Can you explain further?

I understand that the SUMIF formula would work for a single criteria, such as my example. What I’d like to do, however, is define two dates and set up the SUMIFS formula to total everthing between them. The problem exists whether one or two criteria are defined.

I’ll check back to see if this comment goes through okay. If it doesn’t, I’ll put a file on my web page with the information. Thanks, Gary

I’m having trouble with this function when I try to use a reference to a cell with a >= or =G1″,$A$2:$A$7,”=G1″ with the number for that date and “<G2" with the number for that date, it works. I'll download your spreadsheet later and will try it, but it isn't working withi mine. Any ideas?

I’m not 100% clear on your question but I have a feeling that if you try to format your references using >= or =”&A7,criteria2_range,”= are in double quotes and they’re joined to the cell containing the date with the ampersand.

I hope that helped you out, but if I was off the mark please let me know with the exact sample of your formula that isn’t working.

Thanks for the explanation on how to use a cell reference in the expression for the test criteria in the SUMIFS formula. I have been trying for quite some time to find a good explanation on how to use this method of using a reference to a cell as opposed to a constant and I finally managed to make it work.
Thanks.

Yes, there is an alternative to the SUMIFS function if you don’t have Excel 2007 or 2010. It’s called the SUMPRODUCT function and you can see examples of how you can use it to achieve the same results as SUMIFS here:

I want to say your blog is kinda awesome. I always like to read something new about this because I have the similar blog in my Country on this subject so this help´s me a lot. I did a search on the issue and found a good number of blogs but nothing like this.Thanks for writing so much in your blog.. Greets, Adam

Resources

Affiliate Program

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.

✕

Hang On, Don't Go Just Yet.

As a thank you for visiting how would you like a10% Discount Code to use with any of my courses?