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: Before Excel 2007, you had to use the SUMPRODUCT function or a complicated array formula. Starting in Excel 2007, you can use SUMIFS, COUNTIFS, or AVERAGEIFS.
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.
-
Averaging based on three conditions.
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 Fig 414 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.