Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF and SUMPRODUCT help needed | Excel Discussion (Misc queries) | |||
Min/Max help needed with a SumProduct | Excel Worksheet Functions | |||
Sumproduct Help Needed | Excel Discussion (Misc queries) | |||
SUMPRODUCT help needed | Excel Worksheet Functions | |||
Sumproduct help needed! | Excel Worksheet Functions |