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

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

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

Sumproduct query
 

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

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

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.

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< ""))

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< ""))

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

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