=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
--
Biff
Microsoft Excel MVP


"Al9315" wrote in message
...
Hi

Typed it in =INDEX(FREQUENCY(April:May!P5:Z5,9.9999999999),1)

Perfect - a bit beyond my understanding, but it works !!!!

Thank you so much !!!

Al