=INDEX(FREQUENCY(April:May!P5:Z5,9.9999999999),1)
Here's how it works...
FREQUENCY can handle multiple area references and that includes 3d
references.
Basically, FREQUENCY performs a bunch of "count if's" based on the criteria
which are called bins. In this case we have a single bin, 9.9999999999.
FREQUENCY always calculates one more "count if" than the number of bins.
Since we have one bin we'll get back 2 results. If we had 5 bins we'd get
back 6 results.
The "count if's" are based on the values of the bins. The first "count if"
is always: Count if range is less than or equal to bin 1. Since you wanted
to count values less than 10 and the first "count if" does a less than or
equal to we need to make the bin a number less than 10. That's why I used
9.9999999999. It's as close to 10 as we can get and it meets the requirement
of being less than 10.
Ok, so we have our first result: "count if" range <=9.9999999999. Now, as I
said, FREQUENCY always retruns one more "count if" than the number of bins.
Since we have just a single bin and the first result is "count if"
<=9.9999999999 the next result will be "count if" >9.9999999999.
OK, we have our 2 results:
Count if <=9.9999999999 = 6
Count if >9.9999999999 = 3
These results are passed to the INDEX function:
=INDEX({6;3},1)
We want the first result: Count if <=9.9999999999. So we tell INDEX we want
the first result :
=INDEX({6;3},1) = 6
If you wanted the "count if" of values >9.9999999999 then we'd use:
=INDEX({6;3},2) = 3
To sum it up in plain English:
The count of values in the range April:May!P5:Z5 that are less than 10 is 6.
exp101
