SUMIFS() lets you sum values by multiple conditions in multiple columns. But how about applying multiple conditions on single column?

Download Example File at the end of article..

Conditional Summing

Ok, let’s see how this SUMIFS 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:

SUMIFS multiple criteria in same column 02

We see a range with Store, Product, Price, Qty and Sales. The easy part is to get the sum of sales by just clicking the autosum button. But in this case we would like to see the sum of Sales of Product Moto G from Store A. So we are dealing with a multiple conditions here. That’s where SUMIFS can help you.

Also see SUMIFS Formula Explained

The real challenge!

The question we want answered is: we would like to see the sum of Sales from Store A OR Store 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:

SUMIFS multiple criteria in same column 03

The functions seem perfect but Excel gives us an ouput of 0 (‘-‘ when number format is currency). 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 Store 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 us 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:

SUMIFS multiple criteria in same column 04

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 (Learn How to Build an Array Formula). 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 Stores range and sum the Sales. 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. Ta-da… Magic!!

So your final function could look something like this:

=SUM(SUMIFS(F3:F10,B3:B10,{“A”,”C”,”G”}))

That is if you have a G value in your column as well of course.

Download Sample File: SUMIFS Multiple Criteria

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

Also Read

Comments

  1. Vidal Martinez

    I like your blog a lot. Thank you very much for sharing your knowledge.

    Once you are using an array formula, excel evaluate each value in the array as a single condition and acumulate the value in the related range because of the main SUM function. So I solved it using SUMIF instead SUMIF Set (SUMIFS):
    =SUM(SUMIF(B3:B10,{“A”,”C”},F3:F10))

    Thanks again.

  2. Konstantinos Gkitnos

    What if I don’t want to use {“A”,”C”,”G”} and I want to take these values from another sheet or file. How am I suppose to write the code? Thanks!

  3. Guilherme

    Why my SUMIF doesn’t consider values between 0 and 1?
    Range(“O” & X).Value = WorksheetFunction.SumIf(Range(“N16:N501”), “>=” & Range(“N” & X).Value, Range(“N16:N501”)). Being my N16:N501 interval being filled with percentage values, therefore all between 0 and 1, SUMIF doesn’t read them and always returns zero as a result. Any help?

  4. Faris Shahin

    SUMIFS(Actuals!$AD:$AD,Actuals!$A:$A,$B$1,Actuals!$B:$B,IF($B$2=”H1″,{1,2},$B$2))

    AD = Sales Amount
    A = Year
    B = Quarter

    I have my data base in columns, and one of them is showing quarters as (1, 2, 3, 4).
    in my lookup sheet, i created an additional periods as a drop down menu (1,2,H1,3,Q3YTD,4,H2, TY)…
    In the same Sumifs, everything works well with 1, 2, 3, 4, — but i need a way where when i select H1, it can add both 1 and 2… etc…

    can anyone help.

    I need to add multiple quarters

Leave a Reply

Your email address will not be published. Required fields are marked *

9 − 4 =

This site uses Akismet to reduce spam. Learn how your comment data is processed.