Sumif / product help

[smartphreak]

Hi all,

I have a challenge that I cannot quite figure out...

[TABLE="width: 500, align: center"]
<tbody>[TR]
[TD="align: center"]COLA[/TD]
[TD="align: center"]COLB[/TD]
[TD="align: center"]COLC[/TD]
[/TR]
[TR]
[TD="align: center"]Ray[/TD]
[TD="align: center"][/TD]
[TD="align: center"]100,000[/TD]
[/TR]
[TR]
[TD="align: center"]Mary[/TD]
[TD="align: center"]Ray[/TD]
[TD="align: center"]50,000[/TD]
[/TR]
[TR]
[TD="align: center"]John[/TD]
[TD="align: center"]Sam[/TD]
[TD="align: center"]80,000[/TD]
[/TR]
[TR]
[TD="align: center"]Sam[/TD]
[TD="align: center"][/TD]
[TD="align: center"]60,000[/TD]
[/TR]
</tbody>[/TABLE]

I would like to have a sumproduct or sumif formula that can add up the total amount for each person. Where there are two names on a line, they each have 50% of ColC.

Formula should return:
Ray 125,000
Mary 25,000
John 40,000
Sam 100,00

Thanks

 

[njimack]

I'm sure there's a better way, but this works - assuming your summary is in columns E & F, enter the following into F1 (confirm with ctrl+shift+enter) and copy down to F4:
=SUMPRODUCT(--($A$1:$A$4=E1),--($B$1:$B$4=""),($C$1:$C$4))+SUM(IF($A$1:$A$4=E1,IF($B$1:$B$4<>"",$C$1:$C$4/2)))+SUMIF($B$1:$B$4,E1,C$1:$C$4)/2

 

[steve the fish]

The way i just did it. Again CSE:

=SUMPRODUCT(IFERROR((($A$1:$A$10=E1)+($B$1:$B$10=E1))/(($A$1:$A$10<>"")+($B$1:$B$10<>"")),0),$C$1:$C$10)