Sumproduct?

G

Grant

Sheet1 looks like this, Account, Part#, Sales
** Notice some Part# & Account's are the same.
ColumnA,B,C
B15555, 12354949, 80
A16040, 12354959, 120
B15555, 12351001, 84
A16040, 17094000, 36
B15555, 18889955, 14
L17550, 18889955, 12

Sheet2
ColumnA,B,C,D
Part#,A16040, B15555, L17550
12354949
18889955

I want to get the Sales for the Part# in column A for the account listed at
the top. How can I get the value of 120 for Account A16040.

T.I.A.
 
J

Jason Lepack

Sheet1 looks like this, Account, Part#, Sales
** Notice some Part# & Account's are the same.
ColumnA,B,C
B15555, 12354949, 80
A16040, 12354959, 120
B15555, 12351001, 84
A16040, 17094000, 36
B15555, 18889955, 14
L17550, 18889955, 12

Sheet2
ColumnA,B,C,D
Part#,A16040, B15555, L17550
12354949
18889955

I want to get the Sales for the Part# in column A for the account listed at
the top. How can I get the value of 120 for Account A16040.

T.I.A.
Click to expand...

A pivot table will suit your needs just fine.

Jason
 
R

Roger Govier

Hi Grant

If you don't want to use Pivot Tables (which I think is the better
solution) then for Sumproduct
Sheet2 cell C2
=SUMPRODUCT((Sheet1!$A$1:$A$1000=$A2)*(Sheet1!$B$1:$B$1000=B$1)*$C$1:$C$1000)
Copy across and down as required

Change ranges to suit but ensure they are of equal length.
 
Top