How to find the number of rows in an (array formula) range containing evaluated *values*?

[MathUKTeacher]

Hi,

I have an excel sheet with a large range of cells containing an array formula (returning results from an INDEX() query).
I want to determine the number of (dynamically) returned results in the 'list'.
The maximum size of the list is hard-coded by the size of my array formula (~1000 rows at present) that presents the matched responses to the INDEX query - but very frequently there'll be far less matches than this maximum figure and I want to find out how many.

I've tried using COUNTA and COUNTIF but they find the (array) formula in these cells - what I want to count is the actual number of returned / evaluated VALUES that the array formula creates. [I have an ONERROR() formula that handles the fact some formula are unused/un-evaluated, so that whereas they would normally evaluate to 'NOT FOUND' or something, they actually just appear as blank cells.

How do I find the number of actively returned matches (i.e. correctly evaluated formula) so that I can then dynamically assign other stuff! (e.g. randomly select one of them).


Very many thanks for any help. It's for a non-commercial educational project.

UKMathsTeacher

[Tony Valko]

What type of data is returned by the formula? If it's text try this...

=COUNTIF(Range,"?*")

That will count text values excluding formula blanks "".

[MathUKTeacher]

Thanks Tony that's exactly what I wanted. Sorry I hadn't been creative enough to think of that possibility for myself.
That bit's working fine now.

[Tony Valko]

Good deal. Thanks for the feedback!