Calculate Based on Multiple Conditions
March 31, 2022 - by Bill Jelen
Problem: COUNTIF
and SUMIF
have been around since Excel 97. Whenever someone learns how to use these functions, they inevitably come up with a situation where they need to count or sum or based on more than one condition.
Strategy: Starting in Excel 2007, you can use SUMIFS
, COUNTIFS
, or AVERAGEIFS
. The February 2016 release of Office 365 added MAXIFS
and MINIFS
.
Get it? SUMIFS
is the plural version of SUMIF
. It can handle up to 127 different criteria.
Gotcha: Although SUMIF
and SUMIFS
sound the same, Microsoft had to reverse the order of the arguments to make SUMIFS
work. In particular, the Sum_Range argument which was third in SUMIF
has been moved to the first argument in SUMIFS
.
To set up a SUMIFS
or AVERAGEIFS
, use these arguments:
- Sum_Range: The range of numbers to add is specified first.
- Criteria_Range1: A range of values to check.
- Criteria1: The value to look for in Criteria_Range1
- You can then repeat pairs of Criteria_Range and Criteria for each additional condition.
Say that you want to calculate average salary by department and age range. This requires three sets of criteria. The department has to match. Since you want to report on ages by decade, you need to look for ages >=30 and <40.
Note: The data being averaged is similar to the data in the previous several topics. I am not showing columns A:F in the above figure because it would be too small. See Figure 402 for the columns in the data set. The headings are in row 1 and the data is in rows 2 through 57.
The first argument is the range with the values that you want to average. This is F2:F57.
The next pairs of arguments specify that Excel should look through D2:D57 for ages that are greater than zero. Note the single dollar sign before the 1 in I$1. This lets you copy the formula. This argument will always point to the criteria in row 1, but the reference can change to column J, K, L, and M.
The next pair of arguments say to look through the ages in D2:D57 for ages less than 30. Again, the criteria is stored in I$2.
The final pair of arguments say to look through the departments in E2:E57 looking for records that match Accounting as stored in $H3.
Gotcha: The #DIV/0! in M6 is because the sales department has no employees above 59 years of age. (Probably because the sales reps have retired to a private island after earning those huge commissions for so long.) When you AVERAGE
a range that contains no numeric cells, you end up dividing by zero.
This article is an excerpt from Power Excel With MrExcel
Title photo by Eric Prouzet on Unsplash