Hi Brian:
I created a play-file that used the array shown below:
B
C
D
E
*
* */Week #/* */Game #1/* */Game #2 /* */Game #3/*
8
9
1 141 137 135
10
2 97 135 134
11
3 193 172 138
12
4 151 185 125
13
5 197 127 186
14
6 153 138 137
15
7 158 188 134
16
8 132 150 132
Your suggested formula (=SUMPRODUCT($D$9:$F$16>100;$D$9:$F$16<129), with
a modified target range, results returned are:
*<100* *1*
*100 thru 129* *2*
*130 thru 139* *10*
*140 thru 149* *1*
*150 thru 159* *3*
*160 thru 169* *0*
*170 thru 179* *1*
*180 thru 189* *3*
*190 thru 199* *3*
I was expecting to use some sort of a Boolean expression or domain
limits; however, I will read up on =SUMPRODUCT( ... ).
You might have guessed that the array data is derived from my bowling
scores. Since JAN 2008, I have recorded, within a Calc sheet, the scores
for more than 1500 games; lots of data to play with and, at the same
time, learn about using AOO-Calc.
Thank you.
VinceB.
__________________________________________________________________________________________
On 5/20/2016 1:34 PM, Brian Barker wrote:
At 11:01 20/05/2016 -0400, Vince Bonly wrote:
I am using this
=COUNTIF(D342:L380;">100")
... . Possible data values found within D342 and L380 include: 0
through 300. However, what I really want to count is all data values
between 100 and 129, ...
I should have written: "... what I really want is to count all data
values from 100 to 129, inclusive; from 130 to 139, inclusive; etc. etc.
thus yielding an array like this:
<100 1
100 thru 129 2
130 thru 139 10
140 thru 149 1
150 thru 159 3
160 thru 169 0
170 thru 179 1
180 thru 189 3
190 thru 199 3
200 thru 209 0
210 thru 219 0
220 thru 229 0
230 thru 239 0
240 thru 249 0
250 thru 259 0
260 thru 269 0
270 thru 279 0
As an alternative to what has already been suggested, you could use:
=SUMPRODUCT(D342:L380>100;D342:L380<129)
The two comparisons each generate an array of boolean values. When the
array of products is formed, TRUE is interpreted as 1 and FALSE as 0 -
so the result is 1 for each cell for which both criteria are true and
0 otherwise. Summing those 1s effectively counts them and gives you
the result you need.
I trust this helps.
Brian Barker
