2D SUMIF with two variables – one on column and one on row

Excel Tips - 2D sumif

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:

The Syntax


This is the formula used in the above example:

=SUMIF($B$3:$B$16,"Department 1",$C$3:$C$16)
  • 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.

Excel Tips - 2D sumif 1

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.

For example:

If range is And sum_range is Then the actual cells to be summed are
A1:A5 B1:B5 B1:B5
A1:A5 B1:B3 B1:B5
A1:B4 C1 C1:D4
A1:B4 C1:C2 C1:D4

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):


Excel Tips - 2D SUMIF 3          Excel Tips - 2D SUMIF 4

What do you think? Please leave your comment.🙂

This entry was posted in Formula and tagged , , , . Bookmark the permalink.

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s