Sumproduct help needed.
 

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

Sumproduct help needed.
 

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

"Jonathan" wrote in message
...
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

Sumproduct help needed.
 

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)

--
Biff
Microsoft Excel MVP


"Jonathan" wrote in message
...
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

Sumproduct help needed.
 

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)

In article ,
"Jonathan" wrote:

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

Sumproduct help needed.
 

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

"Pete_UK" wrote in message
...
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

"Jonathan" wrote in message
...
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

Sumproduct help needed.
 

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


"Jonathan" wrote:

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