sumif

[carl]

I have a table of data like this:

Product Buyer Seller Qty
P1 Frank Jim 10
A2 Jim Bob 20
A3 Mark Frank 10
P2 Jim Frank 30
P1 Jim Frank 25

I would like to create a new table that will sumif to give
show me how much of each product was traded by each trader.

A2 A3 P1 P2
Frank 0 10 35 30
Jim 20 0 35 30
Mark 0 10 0 0
Bob 20 0 0 0


Thank you in advance.

 

[Juan Sanchez]

Carl

Assuming your data is in

A B C D
1 Product Buyer Seller Qty
2 P1 Frank Jim 10
3 A2 Jim Bob 20
4 A3 Mark Frank 10
5 P2 Jim Frank 30
6 P1 Jim Frank 25


And your matrix
G H I J K
A2 A3 P1 P2
2 Frank
3 Jim
4 Mark
5 Bob

On H2: =SUMPRODUCT(($A$2:$A$6=H$1)*($B$2:$B$6=$G2)*
($D$2:$D$6))+SUMPRODUCT(($A$2:$A$6=H$1)*($C$2:$C$6=$G2)*
($D$2:$D$6))

Fill right
Fill Down

This matches the result of your example...

Cheers
Juan

 

[Andy Wiggins]

This answer puts your results into the range A10:E14. That is, the names are
in A11:A14, the products are in cells B10:E10, and the calcs range is
B11:E14.

In cell B11 put this formula:
=SUMPRODUCT((($A$2:$A$6)=B$10)*(($B$2:$B$6=$A11)+(($C$2:$C$6)=$A11))*($D$2:$
D$6))
then copy it to the remainder of the calcs range.

--
Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"

 

[CLR]

Hi Carl...........

You might take a look at Data > Subtotals............seems like this feature
might do exactly what you want.......

Vaya con Dios,
Chuck, CABGx3