Counting with Criteria

[JCM96]

I have googled and oogles, but with the multitude of counting methods and I haven't found the answer I need.

I'm trying to count the number of case entries (rows) where there are if certain values that can occur in EITHER column. In my example, I want to know how many cases have either A>3 OR B>10... which would be a total of 4 cases. I can get to work for AND but not OR. Please help. Thanks.

A B
3 10
3 0
0 0
0 10
1 0
0 10
0 0
2 5



Here's a link to what I'm trying to explain:
http://img227.imageshack.us/img227/1...lsamplett7.jpg

[WinteE]

Try this :

=SUMPRODUCT((B2:B9>=3)*1)+SUMPRODUCT((C2:C9>=10)*1)-SUMPRODUCT((B2:B9>=3)*(C2:C9>=10)*1)

Erik

[NBVC]

I have googled and oogles, but with the multitude of counting methods and I haven't found the answer I need.

I'm trying to count the number of case entries (rows) where there are if certain values that can occur in EITHER column. In my example, I want to know how many cases have either A>3 OR B>10... which would be a total of 4 cases. I can get to work for AND but not OR. Please help. Thanks.

A B
3 10
3 0
0 0
0 10
1 0
0 10
0 0
2 5



Here's a link to what I'm trying to explain:
http://img227.imageshack.us/img227/1...lsamplett7.jpg

Try:

=SUMPRODUCT(--(((A2:A9>=3)+(B2:B9>=10))>=1))

[pdgood]

Sorry, I just have to ask...
in the last solution entry
=SUMPRODUCT(--(((A2:A9>=3)+(B2:B9>=10))>=1))

what does the -- right after SUMPRODUCT do?

THANKS!

[NBVC]

Sorry, I just have to ask...
in the last solution entry
=SUMPRODUCT(--(((A2:A9>=3)+(B2:B9>=10))>=1))

what does the -- right after SUMPRODUCT do?

THANKS!

it converts arrays of TRUES/FALSES to 1's/0's so that Sumproduct() can do the math necessary to count.

[JCM96]

You guys are awesome! Thanks!