Dynamic range definition in array formula.

**donjoe** · 12-22-2010, 01:26 PM

I have a problem with array formulae and defining dynamic ranges inside them.

I'm trying to get each cell in C1:C10 to show how many cells have numbers in the range Ax:A10, where x=ROW(Cx). So instead of putting separate formulae in each cell like this
C1=COUNT(A1:A10)
C2=COUNT(A2:A10)
...
C10=COUNT(A10:A10)
... I selected the range C1:C10 and just entered this with a CSE at the end:
=COUNT((A1:A10):A10)

I didn't get any error message, but I didn't get the expected result either - all C cells now had the exact same number in them: the total number of cells with numbers, from A1 to A10 (so it was as if I had entered =COUNT(A1:A10) into all the C cells).

What am I doing wrong? What would the array formula have to look like in order to express what I want expressed?

Thanx.

**Ranjoo** · 12-22-2010, 01:33 PM

use formula
COUNT($A1:$A$10)

**shg** · 12-22-2010, 01:38 PM

In C1 and copy down, =ROWS(A1:A$10)

**donjoe** · 12-22-2010, 02:05 PM

@Ranjoo:
Thanks, but that's not an array formula. I really want an array formula to be able to do this because I want to extend it and do more iterative array-based things with the results from those COUNTs. (Specifically, I need to determine what the last 3 numbers are from a range that may contain any number of gaps and may be updated with new values and gaps in the future, and then average those 3 numbers.)

@shg:
Sorry, but that has almost nothing to do with what I asked.