SUMIF is a commonly used function to give you sum of a range that meets a specific criterion. For the example above, if you want to know the total expense of Department 1 in Jan, you can use SUMIF:
This is the formula used in the above example:
- where B3:B16 is the range with the criteria;
- “Department 1” is what you look for;
- C3:C16 is the range of the data you need to add
Altogether, it means please give me the total expense of Department 1 in Jan.
Instead of “hard-coding” the Department, we can substitute “Department 1” with the cell reference $G$3 where the department will be input to:
With this modification, we can get the result for different departments by changing the value in G3. That is easy.
Could we also add the second variable for Month, so that we can get the answer for different month according to the value input in H3?
The answer is YES! Thanks to the interesting characteristic of the sum_range argument.
The sum_range argument does not have to be the same size and shape as range. The actual cells that are added are determined by using the top, left cell in sum_range as the beginning cell, and then including cells that correspond in size and shape to range.
|If range is||And sum_range is||Then the actual cells to be summed are|
Now look back at our example. The range is B3:B16, we may input C3 as the sum_range for the month of Jan; D3 for Feb; and E3 for Mar.
The actual cells to be summed for the above formula are C3:C16 (which is the same size of the range).
Now with this “simplified” sum_range, we may make it dynamic by using INDEX and MATCH:
where MATCH is used to identify the position of the Month input in H3.
When H3 is Jan, Feb or Mar, it returns 1, 2 or 3 respectively.
Wrapped with INDEX,
- INDEX(C3:E3,1) returns a cell reference of C3
- INDEX(C3:E3,2) returns a cell reference of D3
- INDEX(C3:E3,3) returns a cell reference of E3
Thanks to the reference form syntax of INDEX.
By putting all together, this is the final formula to make the SUMIF dynamic with 2(D) variables (G3 for department; H3 for month):
What do you think? Please leave your comment. 🙂