SUMIFS – SUMIF FOR MULTIPLE CRITERIA
SUMIFS can be treated as a SUMIF for multiple criteria.
Recently I was asked for help on a file where SUMIF formula were used to add cells specified by certain criteria.
The user wanted to create a SUMIF for multiple criteria though, and that is where SUMIFS came into it.
Click here to read my prior post on SUMIF if you are unsure of them, a basic understanding of them will help understanding this post.
Note: This is not the only way to complete what was required (PivotTables, DB functions, etc.) could be used – but for this particular case I chose SUMIFS.
The file we are going to use for this post will be similar to the one below.
- We have 3 groups of asset (A, B + C)
- We do not want to include all of them in our total, so column C will highlight what we do want to include.
- For those that we do want to include, we need them broken out by category (column A).
A SUMIF would give us the total for a specific group, category, or include/exclude flag – but it won’t give us the total for certain group and category and include/exclude.
The SUMIFS funciton will give us what we would want from a SUMIF with multiple criteria.
SUMIFS with 3 criteria
SUMIFS “adds the cells specified by a given set of conditions or criteria”.
The syntax is =SUMIFS(sum_range, criteria_range1, criteria1,…).
You can add more criteria to this.
Let’s start in cell G3.
- The sum_range is column D – the cells that we want to add based on our criteria
- criteria_range1 is column A – our first criteria will be the category
- criteria1 is F3 – our first criteria will be “EQUIPMENT”
- criteria_range2 is column B – we are now going to set criteria based on the group
- criteria2 is “A” – our second criteria is group A
- criteria_range3 is column C – we are now going to set criteria based on the include/exclude flag
- critera3 is “YES” – we want to include the items marked for inclusion
I then make all columns absolute/mixed references (with $ signs before the column letters) and this is the first formula.
Putting the $ in front of the column letters makes it easier to copy this formula to other rows and columns.
The reference to cell F3 should be “$F3” – we want the row number to change, but we want the column to remain the same.
This first SUMIFS formula is going to add the values in column D where, the category in A is “EQUIPMENT”, the group in B is “A” and column C is marked for inclusion.
When I copy the formula to other groups, I just need to change the group letter in critera2.
SUMIFS with 2 criteria
All the above SUMIFS had 3 criteria, but you do not always have to have 3 criteria/conditions.
To get a total for the items marked not to include, we will create a SUMIFS formula with just 2 criteria, the group letter and marked “NO” for Include/Exclude.
Hopefully you understand the SUMIFS formula to try this one yourself.
Below is the formula as it would appear in cell G11 of my example. Again, I have made references absolute/mixed so that I can copy the formula to other cells.
Is that what you would have entered too?
As you work with Excel you realise that there is usually more than one way to do pretty much the same thing.
In my own files, I use PivotTables for my scenarios similar to above, this would not have been ideal for the file I was looking at with another user.
I could probably also use DB functions, array formula, or even a SUMIF formula based on a string that makes up the multiple conditions above. They too may also have worked, but I chose SUMIFS.
