SOLVED

COUNTA copy down column

New Contributor

Hi, 

 

I am using COUNTA to give me a number for a specific range A2:A96. Is there a way that when I copy the formula down a column for it to change the range to the next n number of rows? Each 95 rows of the data set represents another user and I am trying to calculate the number of non blank rows for each user. So ideally it would be like COUNTA(A2:A96) then COUNTA(A97:A191) and so on. 

 

Thanks 

2 Replies
best response confirmed by athakore (New Contributor)
Solution

@athakore 

Let's say the first formula is in cell D3. Change the formula to

 

=COUNTA(OFFSET($A$2:$A$96,95*(ROW(D3)-ROW($D$3)),0))

 

and fill or copy down.

thank you so much. I was trying to play around with offset but could not get it to work. This worked perfectly.