Index match function - multiple criteria summed in one cell

Sjanzie

New Member
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

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.
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)))

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

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

Replies
1
Views
215
Replies
1
Views
341
Replies
12
Views
842
Replies
1
Views
218
Replies
3
Views
312

1,214,262
Messages
6,118,551
Members
448,835
Latest member
Profast123

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.

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

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