Sumproduct query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Scrunch - 13 Oct 2008 18:58 GMT

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 to this Message

Gary''s Student - 13 Oct 2008 19:20 GMT

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

Signature

Gary''s Student - gsnu200806

> Please could someone help me with a SUMPRODUCT formula? I have the following
> data:
[quoted text clipped - 15 lines]
>
> I hope someone can help! Thanks

Reply to this Message

Sandy Mann - 13 Oct 2008 19:23 GMT

Try:

=SUMPRODUCT(((B2:B8<10)*(B2:B8<>""))+((C2:C8<2)*(C2:C8<>"")))

But this returns 7 not 5: B5, B6, B7, B8, C3, C7 & C8

Signature

HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk

> Please could someone help me with a SUMPRODUCT formula? I have the
> following
[quoted text clipped - 16 lines]
>
> I hope someone can help! Thanks

Reply to this Message

Sandy Mann - 13 Oct 2008 19:34 GMT

As Gary"s Student's solution shows, you don't want to count duplicates - go
with his solution.

Signature

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk

> Try:
>
[quoted text clipped - 23 lines]
>>
>> I hope someone can help! Thanks

Reply to this Message

Sandy Mann - 13 Oct 2008 19:49 GMT

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.

Signature

HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk

> As Gary"s Student's solution shows, you don't want to count duplicates -
> go with his solution.
[quoted text clipped - 27 lines]
>>>
>>> I hope someone can help! Thanks

Reply to this Message

Glenn - 13 Oct 2008 19:53 GMT

> Please could someone help me with a SUMPRODUCT formula? I have the following
> data:
[quoted text clipped - 15 lines]
>
> 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.

Reply to this Message

Glenn - 13 Oct 2008 20:02 GMT

>> Please could someone help me with a SUMPRODUCT formula? I have the
>> following data:
[quoted text clipped - 21 lines]
>
> 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 to this Message

Sandy Mann - 13 Oct 2008 20:34 GMT

> 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.

Signature

HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk

>>> Please could someone help me with a SUMPRODUCT formula? I have the
>>> following data:
[quoted text clipped - 24 lines]
>
> =SUM(OR((B3:B9<10),(C3:C9<2))*(B3:B9<>"")*(C3:C9<>""))

Reply to this Message

Scrunch - 13 Oct 2008 23:30 GMT

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

Reply to this Message

ShaneDevenshire - 13 Oct 2008 23:51 GMT

Signature

Thanks,
Shane Devenshire

> Please could someone help me with a SUMPRODUCT formula? I have the following
> data:
[quoted text clipped - 15 lines]
>
> I hope someone can help! Thanks

Reply to this Message