How to sum values from multiple tables, with different categories, into a final table for the total

Question

Me and my friends are going on a trip, and we have different costs associated, but due to ease of life, a certain person will pay for items initially before we calculte the splits and balances due.

My question is regarding the summation. Is there a function similar to sumif that can read a range for a name mentioned multiple times, and return a summation of the value figure next to each mention of the name?

The inserted image shows how the split looks. I attempted to use sumif, but it will take so much manual range setting, I found it easier to manually choose the individual cells and a simple sum function, to assist in filling the "total to spend table".

Is there a function that can assist me here?

If this is possible I could apply this to my work, so this simple cost split can assist me greatly in the future.

Bonus - if something can be suggested to assist us in calculating how to send our money accordingly for the split, I would be greatful.

Answer 1

In case you are running Excel 365, then in cell H26 apply the following formula:

=LET(vs, VSTACK(B2:C5, E2:F5, H2:I9, K2:L6, N2:O9, B14:C21, E14:F21), gb, GROUPBY(TAKE(vs,, 1), DROP(vs,, 1), SUM,, 0), tk, TAKE(gb,, 1), HSTACK(tk, DROP(gb,, 1) - INDEX(F26:F33, MATCH(tk, E26:E33, 0))))

Hope this helps.

Answer 2

Hi,

Why have 7 different tables. In each table just have an additional column of Type of expense and then create a single table from the 7 tables. You will then be able to use Pivot Tables/formulas easily.

Answer 3

If you are not running Excel 365 then you can use the following formula in cell I26 and drag it down.

=SUM(IFERROR(INDEX($C$2:$C$5, MATCH(E26, $B$2:$B$5, 0)), 0), IFERROR(INDEX($F$2:$F$5, MATCH(E26, $E$2:$E$5, 0)), 0), IFERROR(INDEX($I$2:$I$9, MATCH(E26, $H$2:$H$9, 0)), 0), IFERROR(INDEX($L$2:$L$6, MATCH(E26, $K$2:$K$6, 0)), 0), IFERROR(INDEX($O$2:$O$9, MATCH(E26, $N$2:$N$9, 0)), 0), IFERROR(INDEX($C$14:$C$21, MATCH(E26, $B$14:$B$21, 0)), 0), IFERROR(INDEX($F$14:$F$21, MATCH(E26, $E$14:$E$21, 0)), 0)) - INDEX($F$26:$F$33, MATCH(E26, $E$26:$E$33, 0))

I hope to receive a reply from you to let me know whether your problem has been resolved or not.