Thread
:
Sumproduct query
View Single Post
#
8
Posted to microsoft.public.excel.worksheet.functions
Sandy Mann
external usenet poster
Posts: 2,345
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< ""))
Reply With Quote
Sandy Mann
View Public Profile
Find all posts by Sandy Mann