EXCEL: SUMIFS – DEMYSTIFIED Don’t you just love SUM? The Excel development team does, they even gave it a huge button in the ribbon. If you need to sum up a range of numbers you just click the AutoSum button and you are done. Working with Sum just made our lives just that more convenient. So you are ready for the next steps with SUM and are looking for a sum of values based on a condition. Enter SUMIF. Note: my regional settings require a ; (semi column) as a argument delimiter. Depending on your settings this can also be a , (comma). So replace accordingly in your functions.

Conditional Summing

Ok, let’s see how this SUMIF function works. We have a small range in Excel and would like to sum the values based on a condition in that range. Take a look at the following scenario in the image below: We see a range with shelf, quantity, color and size. The easy part is to get the sum of quantity by just clicking the autosum button. But in this case we would like to see the sum of quantity from shelf A. So we are dealing with a condition here. That’s where SUMIF can help you.

SUMIF takes three arguments:

• What is the range I have to look in
• What is the condition I have to look for in the range you provided in step 1
• What is the range I have to sum

Now look at the image again. I have outlined the ranges in the SUMIF for you. I double check by clicking the cells in the quantity column which meet my criteria (Ctrl+click). Excel shows me the result in the statusbar. This is correct! Simple right? Add this function to your toolbox now.

Conditional Summing with two or more conditions

We take our summing to the next level. We use the same scenario but add another condition. So we are stuck with two conditions. SUMIF does not allow for two conditions (at least not with some tricks or cheating). If you are using Excel 2007, 2010, 2013 or beyond…. and you probably are, you have the possibility to use the SUMIFS function. This specific function is designed to take more conditions.

NOTE: if you are using SUMIFS for the first time you will notice that the order of arguments has changed slightly. Be aware of that!

Same scenario as described earlier but this time we want to see the sum of quantity of shelf A where the color is Red. Take a look at the image below: The most important thing to notice is the order of arguments. In this case the function wants to know the following arguments:

• what is the range I have to sum
• what is the first range I have to look in
• what is the first condition I have to look for in the range you specified in the previous step
• what is the second range I have to look in
• what is the second condition I have to look for in the previous range you provided.
• and so on…..

to double check I Ctrl+click every cell in the quantity column which meets my criteria and look in the statusbar. Yep the sum is the same as the output of my function! We are on a roll here… So add this to your toolbox as well.

Finally the challenge

Now for our last scenario we seem to be asking a lot of Excel because we don’t get the ouput we expect. What’s wrong… what are we doing wrong here and how to solve this.

The question we want answered is: we would like to see the sum of quantity from shelf A OR shelf C. Fairly easy question right? Excel doesn’t seem to think so. Based of what we have learned so far we start our SUMIFS function and fill in the variables as described. See image below: The functions seem perfect but Excel gives us an ouput of 0. You check once more.. change the function a bit and decide to write it from start again. Talking to yourself out loud to hear if you are asking the correct question. Recognize this?

Here’s the issue. SUMIFS sees your function as a AND situation and that is not what we are looking for. So technically Excel is looking for a shelf AC which doesn’t exists and therefore returns 0. To be more blunt… SUMIFS is always an AND combination. It does not work with an OR condition. In this case we are looking in the same column for two conditions. Although it sounds like a AND situation we are technically looking for an OR situation.

So how do we solve this?

Many of my students like the SUMIFS function. They want to use it no matter what. They don’t like to hear ‘You can also use this function or calculate it some other way‘. SUMPRODUCT is often a function that will fly by when discussing the SUMIFS. We will use the SUMIFS for the OR situation. take a look at the image below: The trick we are using here is the use of an Array. It goes beyond the scope of this post to explain this completely here. But to summarize you start with a regular SUM function which is followed by a SUMIFS. The arguments for the SUMIFS are provided. So look in the shelfs range and sum the quantity. The last argument is the ‘new’ argument. You place your search conditions between curly brackets. This is where you define the OR situation. Because the SUMIFS does not ‘get’ this it will provide single values based on those two conditions. The regular SUM function which you started with will sum these single calculated values to a total sum!

Add another value. If you want to add another value to your search condition in the same column you just add it to the curly brackets and press Enter. Voila (that’s French for there you go :-)).

So your final function could look something like this:
=SUM(SUMIFS(Q16:Q33;P16:P33;{“A”;”C”;”F”})) – that is if you have a F value in your column as well of course.

Now add that one to your toolbox as well and you are good to go SUMMING.

13 thoughts on “EXCEL: SUMIFS – DEMYSTIFIED”

1. Maurice Post author

Thank you.. appreciated 🙂

1. Maurice Post author

Thanx Sasa!

1. Jim

Nice article! It wasn’t clear from the article, but you can’t just type the braces {}….you need to enter as an array formula by pressing CTRL+SHIFT+ENTER. My version of Excel will not allow typing the braces – in fact it gives you an error message as you type if you try to enter them manually.

1. Maurice Post author

Thanks for the feedback Jim. It is indeed Ctrl+Shift+Enter to make an array out of it!

2. Ryan

Holy cow, thank you so much for this! I had been stuck for about 4 hours (with some breaks of course) now on how I could count the amount of individual cards in my Magic deck even though I only listed one entry for each card (and a quantity in a different column). I could count all sorts of things, but couldn’t do much at once. This was exactly what I needed.

3. Taylor

I was trying to return a dollar amount based on dates before today and based on one of two different criteria from a drop down selection in the table. I discovered that if I wrote separate sumifs in two different cells, based on the date and only one of the two other criteria, I could simply sum those two cells. I hid those columns later since I did not need them.
The formula in the cell I wanted: =K1+K2
The Formulas in those cells: =SUMIFS(Table1[Amount],Table1[MAS Date],”<="&TODAY()-30,Table1[Status],J2)
and
=SUMIFS(Table1[Amount],Table1[MAS Date],"<="&TODAY()-30,Table1[Status],J3)
Hope that helps for those of you trying to create like a sumifs "and or or." I did something similar for an effective sumifs "and and or or."

4. Aman

The above formula was a great help. Thanks for the same.

Can you also help with the similar situation for AVERAGEIFS.

1. Maurice Post author

Hi Aman,
Thank you for your feedback. I will also write a post about averageifs.

1. Aman

Maurice, please share the link with me once you share that post. I am waiting for the same eagerly.

5. Beniamin Stoica-Fuchs

Thank you! Much appreciated help. This solved my problem… I was trying to sum values based on criteria from one field + multiple criteria from another field. Tried with OR (seemed like a logical solution)… didn’t work as expected. Tried with with your solution… worked like a charm 🙂

6. Chimane

Good day Maurice,
How does one replace the array {“A”;”C”;”F”} with cell references?