Index match function - multiple criteria summed in one cell

[Sjanzie]

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!

 

[jasonb75]

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

 

[Sjanzie]

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

It's giving an error unfortunately..

 

[jasonb75]

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