Another COUNTIF

J

Jon

I have a range F52:H73, I am trying to count the numbers>0,and <125.

I came up with
=COUNT(IF(F52:H73<>0<125,F52:H73))

Range looks like
Game 1 Game 2 Game 3
120 146 95
133 147 50
0 0 0
166 146 135
0 0 0
121 159 168
123 170 125
0 0 0
121 121 121
155 145 140
140 167 137

Any help is greatly appreciated.
 
B

bpeltzer

=COUNTIF(F52:H73>0,F52:H73)-COUNTIF(F52:H73>=125,F52:H73)
This counts everything above 0, then subtracts the count of everything 125
or greater.
 
D

Duke Carey

For a multiple condition count you have to change functions

=SUMPRODUCT(--(F52:H73>0),--(F52:H73<125)

To explain:
(F52:H73>0) generates an array of TRUE/FALSE values corresponding to each
cell in the range and whether each cell's value passes or fails the test.
The double negatives convert the trues to 1s and the falses to 0s. Same for
the other test. The sumproduct function multiples the arrays together.
Only the cells that pass both tests will resolve to a 1, for all the other
cells represented in the arrays will have at least a single zero, and zero
times anythng is...
 
J

Jon

Duke,

Thanks that did it. I think I understand your explanation. New to Excel
and Loving it.

Thanks again.
 
B

Bob Phillips

=COUNTIF(F52:H73,">0")-COUNTIF(F52:H73,">=125")

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Top