SUMIFS() lets you sum values by multiple conditions in multiple columns. But how about applying multiple conditions on single column?
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 Quantity Sold, Product and Salesperson. The easy part is to get the sum of Quantity Sold by just clicking the autosum button. But in this case we would like to see the sum of Quantity Sold of Product “Apples” from Salesperson “Waqas”. So we are dealing with a multiple conditions here. That’s where SUMIFS can help you.
So your final function could look something like this:
But here is the real challenge!
The question I want answered is: I would like to see the sum of “Quantity Sold” from different salespersons like ” Waqas , Shafqat and Amir” at the time. 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 Salesperson “ShafqatWaqasAmir” as one word 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 three 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. 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 Salesperson range and sum the Quantity Sold. 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:
That’s it You have Done it.
0 Comments