The first table below shows how much each person owes and who pays it (it's part of a larger model so I simplified it for our purposes here).

Our goal in the second table below is to give a sum when both the column and row value match.

For example: A (column C) paid $244.17 (D36:H48) in expenses for B (row 35).

Where am I wrong here? I have tried different methods suggested here.

CodePudding user response：

This is another alternative, that only requires to extend the formula down, but not to the left, because on each row it returns an array with all column values. In cell I3 enter the following formula:

=MMULT(N(TRANSPOSE($A$3:$A$15=H3)),IF($B$3:$F$15="", 0, $B$3:$F$15))

or using LET to avoid repetition of the same range:

=LET(set, $B$3:$F$15, MMULT(N(TRANSPOSE($A$3:$A$15=H3)),IF(set="", 0, set))

Notes:

• MMULT only works with numeric values, so empty cells need to be converted.
• You can replace TRANSPOSE with TOROW if you want.
• $-notation is not required in H3, because we extend the formula only down

Here is the output:

Note: This solution assumes header values to compare are the same, i.e. same values for Paid For (I2:M2) and Paid By (H3:H7). Which is the most common situation. That is why in the formula only Paid By column is used. If that is not the case, then the solution provided by @JB-007 is more flexible, because the values can be different, but then you need to extend the formula in both directions.

CodePudding user response：

screenshot/s