Index match function - multiple criteria summed in one cell

Sjanzie

New Member
Joined
Jul 30, 2014
Messages
3
For management reporting purposes, I have an extensive list of accounts I need to index match to a different format. But because we have a more or less fixed overview, some accounts should be summed/consolidated into one line. To make it a bit more clear: I want to have two index match results summed in one cell and not having to double enter the formula for a single index match. The reason I want a short and smart formula, is that I might have accounts with 3, 4 or even 8 accounts as well. So something like a "index, match and sum the accounts in this range for this flexibly chosen month".

So if the first line only has one account to match (row 15), I just insert one criteria and the result will be just that. (I leave the second criteria column blank, so it won't match anything.)
If the second line (row 16) has two matches to make, I insert the first criteria in column H and the second criteria in column I.

I have attached an example, where I would like to see 150 in C16 :)

Thanks a lot in advance for your help!

Example.JPG
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,637
Office Version
  1. 365
Platform
  1. Windows
Try

=SUMPRODUCT(SUMIF(INDEX($C$4:$E$9;0;MATCH($H$4;$C$3:$E$3;0);$B$3:$B$9,$H$6:$I$6)))
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,637
Office Version
  1. 365
Platform
  1. Windows
Sorry, typo

=SUMPRODUCT(SUMIF(INDEX($C$4:$E$9;0;MATCH($H$4;$C$3:$E$3;0);$B$4:$B$9,$H$6:$I$6)))
 

Watch MrExcel Video

Forum statistics

Threads
1,129,700
Messages
5,637,882
Members
416,988
Latest member
Ahmed_Yehia

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top