Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct query
Please could someone help me with a SUMPRODUCT formula? I have the following
data: A B C 1 Window Summer Winter 2 W1 20 4 3 W2 15 1 4 W3 5 W4 8 3 6 W5 8 2 7 W6 8 1 8 W7 0 0 I need a formula that will count the number of windows with with EITHER a summer value <10 OR a winter value <2. It should count zeros but not blank cells. In the above example the answer should be 5. I hope someone can help! Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct query
A helper column is easier:
In cell D2 enter: =OR((B2<10),C2<2)*NOT(OR((B2=""),(C2=""))) and copy down to see: Window Summer Winter W1 20 4 0 W2 15 1 1 W3 0 W4 8 3 1 W5 8 2 1 W6 8 1 1 W7 0 0 1 Then just =SUM(D:D) to get your 5 -- Gary''s Student - gsnu200806 "Scrunch" wrote: Please could someone help me with a SUMPRODUCT formula? I have the following data: A B C 1 Window Summer Winter 2 W1 20 4 3 W2 15 1 4 W3 5 W4 8 3 6 W5 8 2 7 W6 8 1 8 W7 0 0 I need a formula that will count the number of windows with with EITHER a summer value <10 OR a winter value <2. It should count zeros but not blank cells. In the above example the answer should be 5. I hope someone can help! Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct query
Try:
=SUMPRODUCT(((B2:B8<10)*(B2:B8<""))+((C2:C8<2)*(C 2:C8<""))) But this returns 7 not 5: B5, B6, B7, B8, C3, C7 & C8 -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Scrunch" wrote in message ... Please could someone help me with a SUMPRODUCT formula? I have the following data: A B C 1 Window Summer Winter 2 W1 20 4 3 W2 15 1 4 W3 5 W4 8 3 6 W5 8 2 7 W6 8 1 8 W7 0 0 I need a formula that will count the number of windows with with EITHER a summer value <10 OR a winter value <2. It should count zeros but not blank cells. In the above example the answer should be 5. I hope someone can help! Thanks |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct query
This SUMPRODUCT() formula may do what you want:
=SUMPRODUCT(((B2:B8<10)+(C2:C8<2)0)-((B2:B8="")*(C2:C8=""))) It is also tolerant of a value in one Column and a blank in the second one if that could be a condition. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Sandy Mann" wrote in message ... As Gary"s Student's solution shows, you don't want to count duplicates - go with his solution. -- Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Sandy Mann" wrote in message ... Try: =SUMPRODUCT(((B2:B8<10)*(B2:B8<""))+((C2:C8<2)*(C 2:C8<""))) But this returns 7 not 5: B5, B6, B7, B8, C3, C7 & C8 -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Scrunch" wrote in message ... Please could someone help me with a SUMPRODUCT formula? I have the following data: A B C 1 Window Summer Winter 2 W1 20 4 3 W2 15 1 4 W3 5 W4 8 3 6 W5 8 2 7 W6 8 1 8 W7 0 0 I need a formula that will count the number of windows with with EITHER a summer value <10 OR a winter value <2. It should count zeros but not blank cells. In the above example the answer should be 5. I hope someone can help! Thanks |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct query
Scrunch wrote:
Please could someone help me with a SUMPRODUCT formula? I have the following data: A B C 1 Window Summer Winter 2 W1 20 4 3 W2 15 1 4 W3 5 W4 8 3 6 W5 8 2 7 W6 8 1 8 W7 0 0 I need a formula that will count the number of windows with with EITHER a summer value <10 OR a winter value <2. It should count zeros but not blank cells. In the above example the answer should be 5. I hope someone can help! Thanks Another option: =SUM((B2:B8<10)*(B2:B8<"")+(C2:C8<2)*(B2:B8=10)* (C2:C8<"")) Enter with CTRL+SHIFT+ENTER because this is an array formula. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct query
Glenn wrote:
Scrunch wrote: Please could someone help me with a SUMPRODUCT formula? I have the following data: A B C 1 Window Summer Winter 2 W1 20 4 3 W2 15 1 4 W3 5 W4 8 3 6 W5 8 2 7 W6 8 1 8 W7 0 0 I need a formula that will count the number of windows with with EITHER a summer value <10 OR a winter value <2. It should count zeros but not blank cells. In the above example the answer should be 5. I hope someone can help! Thanks Another option: =SUM((B2:B8<10)*(B2:B8<"")+(C2:C8<2)*(B2:B8=10)* (C2:C8<"")) Enter with CTRL+SHIFT+ENTER because this is an array formula. Or, better yet... =SUM(OR((B3:B9<10),(C3:C9<2))*(B3:B9<"")*(C3:C9< "")) |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct query
"Glenn" wrote in message
... Or, better yet... =SUM(OR((B3:B9<10),(C3:C9<2))*(B3:B9<"")*(C3:C9< "")) If the OP's data could have data that meets the criteria in one Column and a blank in the other then your original formual is better because this one fails to count that item. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Glenn" wrote in message ... Glenn wrote: Scrunch wrote: Please could someone help me with a SUMPRODUCT formula? I have the following data: A B C 1 Window Summer Winter 2 W1 20 4 3 W2 15 1 4 W3 5 W4 8 3 6 W5 8 2 7 W6 8 1 8 W7 0 0 I need a formula that will count the number of windows with with EITHER a summer value <10 OR a winter value <2. It should count zeros but not blank cells. In the above example the answer should be 5. I hope someone can help! Thanks Another option: =SUM((B2:B8<10)*(B2:B8<"")+(C2:C8<2)*(B2:B8=10)* (C2:C8<"")) Enter with CTRL+SHIFT+ENTER because this is an array formula. Or, better yet... =SUM(OR((B3:B9<10),(C3:C9<2))*(B3:B9<"")*(C3:C9< "")) |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct query
Thanks to all three of you that have posted. You've been a great help.
My data shouldn't have a blank on one column and not in the other but all your posts have proved very helpful. Cheers |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct query
-- Thanks, Shane Devenshire "Scrunch" wrote: Please could someone help me with a SUMPRODUCT formula? I have the following data: A B C 1 Window Summer Winter 2 W1 20 4 3 W2 15 1 4 W3 5 W4 8 3 6 W5 8 2 7 W6 8 1 8 W7 0 0 I need a formula that will count the number of windows with with EITHER a summer value <10 OR a winter value <2. It should count zeros but not blank cells. In the above example the answer should be 5. I hope someone can help! Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMPRODUCT query | Excel Discussion (Misc queries) | |||
sumproduct query | Excel Worksheet Functions | |||
SUMPRODUCT IF query | Excel Discussion (Misc queries) | |||
SUMPRODUCT Query | Excel Discussion (Misc queries) | |||
Sumproduct Query | Excel Discussion (Misc queries) |