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