Proper use of SUMPRODUCT to split expenses?

Hi everyone, what's the proper way to sum up the total cost of each individual person (using sumproduct or otherwise)?

The worksheet looks like this:

	A	B	C	D	E	F
1	Expense	Cost	PersonA	PersonB	PersonC	PersonD
2	Restaurant	$150	1	1	1	1
3	Movies	$50	1	1	1	1
4	Gas	$30	1		1
5	Souvenirs	$10	1			1

A "1" in a person's column indicates that expense cost is evenly split to them. Blank indicates they were not part of that expense.

I have another table like this to summarize:

	Owe
PersonA
PersonB
PersonC
PersonD

I think in the Owe column, the formula should be something like

=SUMPRODUCT(B:B, C:F, --(C1:F1 = (cell with person's name)), (cell of person divided by sum of each row))

but I haven't been able to figure it out.

Thanks in advance!

Are you saying that a quarter of the Restaurant cost (i.e. $37.50) is allocated to each person, but half of the Gas cost (i.e. $15.00) is allocated only to PersonA and PersonC ?

Pete

I set this up as you have shown it, with "personA" in cell A10 in your lower table, and then you can use this formula in B10:

=SUMPRODUCT($B$2:$B$5*(INDEX($C$2:$F$5,0,MATCH(A10,$C$1:$F$1,0))/$G$2:$G$5))

Copy this down to B13.

Hope this helps.

Pete

Thanks Pete. G2:G5 would be the sum of their respective row?

That solution works!

Just curious, would it be possible to do it without added an additional column to the data?

Yes, sorry I forgot to mention that I added this formula to G2:

=SUM(C2:F2)

and then copied it down. I think it would be a much more awkward formula without that helper column - you can always hide the column if you don't want to see it.

Hope this helps, and thanks for the rep.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Pete