Sumproduct help needed.

[Jonathan]

I'm trying to get a formula in Excel that will add up e.g. that there are
£22.00 of screws type A and £13.00 of screws type B. Then £10.50 of nuts
type D and £6.50 of nuts type A. I think I need sumproduct but I'm not an
expert.

Can anyone help please?


A B C D
1 Screws 10.00 A
2 Screws 12.00 A
3 Screws 13.00 B
4 Nuts 5.00 D
5 Nuts 5.50 D
6 Nuts 6.50 A


TIA
John

 

[Pete_UK]

You might like to put "A", "B" and "D" in E2:E4 and "Screws" in F1 and
"Nuts" in G1 (without the quotes), and then put this formula in F2:

=SUMPRODUCT(($A$1:$A$6=F$1)*($B$1:$B$6=$E2)*($C$1:$C$6))

Then you can copy F2 into G2, and F2:G2 down into the next two rows.

Hope this helps.

Pete

 

[T. Valko]

Try it like this:

=SUMPRODUCT(--(A1:A6="screws"),--(C1:C6="A"),B1:B6)

Better to use cells to hold the criteria:

E1 = screws
F1 = A

=SUMPRODUCT(--(A1:A6=E1),--(C1:C6=F1),B1:B6)

 

[JE McGimpsey]

You may be better off with a Pivot Table. But if you want a worksheet
function:

=SUMPRODUCT(--(A1:A6="Screws"), --(C1:C6="A"), B1:B6)

 

[Pete_UK]

I can see from Biff's reply that I got the columns mixed up - try this:

=SUMPRODUCT(($A$1:$A$6=F$1)*($C$1:$C$6=$E2)*($B$1:$B$6))

I should pay more attention !! <bg>

Pete

 

[Elkar]

Try this:

=SUMPRODUCT(--(A1:A6="Screws"),--(C1:C6="A"),B1:B6)

This will give you the total amount for Type A Screws. Adjust the "Screws"
and "A" portions accordingly to get your other answers.

HTH,
Elkar