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
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,942
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
11,942
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,122,564
Messages
5,596,875
Members
414,106
Latest member
Tigretto

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