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

SUMIF(range,criteria,sum_range)

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:

=SUMIF($B$3:$B$16,$G$3,$C$3:$C$16)

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.

=SUMIF($B$3:$B$16,$G$3,$C$3)

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:

=INDEX($C$3:$E$3,MATCH($H$3,$C$2:$E$2,0))

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

=SUMIF($B$3:$B$16,$G$3,INDEX($C$3:$E$3,MATCH($H$3,$C$2:$E$2,0)))

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.

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

  1. Excel Analyst says:

    Thank you for the detailed walkthrough! You just save me a few hours.

    Like

    • MF says:

      You are welcome. Glad it helps

      Like

      • Gergana says:

        Hi Thank you for your excel support I would like to ask you if I have to sum by 3 criteria For example I have 1st column month second column project number 3td column accounts number and 4th column sum In another worksheet I have to fill in the sum for defined accounts (which I should take from 3th column) and match by month and project number Thanks for your help

        Like

        • MF says:

          Perform VLOOKUP with 2 lookup values | wmfexcel
          https://wmfexcel.com/2014/05/11/perform-vlookup-with-2-lookup-values-2/
          If you get this, you may try to do it with additional value (column)
          Alternative Pivot Table maybe an easier way.

          Like

          • Gergana says:

            Hi – Thank you for your really fast answer. I know for the pivot table, but it is not working for me unfortunately. VBA will, but I would like to do it for now with excel functions. In fact I have 2 files – one is with the data I already explained – columns: month, project number, accounts, 4-th column sum. In the second file I have more columns: 1st column month, 2nd column project number, 3th column external cost (there I should have the sum of some of the accounts in the 3thd column from the 1st file), 4th row internal costs (sum of some of the accounts from the 3th column from the 1st file) etc… (something like transposing sum of rows) One decision is to use helper column in the first file – where to sum of the accounts I need – but I try to learn now to use complex formula and think that with sumifs and index match I could make it – but somewhere I’m doing wrong and didn’t get it I really appreciate if you help for syntax the formula

            Like

          • MF says:

            Hi Gergana,
            It’s a bit difficult for me to “visualize” your data and understand your request… Perhaps you may give an example with some dummy data and state the layout you want as output?
            Alternative, you may put your question to MrExcel forum http://www.mrexcel.com/forum/forum.php (my all time favourites), where lots of Excel experts are willing to help unconditionally. 🙂
            Cheers,

            Like

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