How to count (not sum) every N th cell

[hwq729]

Hi, I worked on a excel sheet, which has 1429 rows. Every 21 rows means one set of data for an individual (e.g. 1-21 for one individual, 22-42 for another individual).

I need to Count (not sum) every 21 cells. Currently, I am using Count function, however, if I drag it down, it counts 2nd cell to 22 cell, but not the 22nd cell to the 42nd cell.....

 

[Rick Rothstein]

Give this non-Volatile formula a try...

=COUNT(INDEX(A:A,1+21*(ROWS(A$1:A1)-1)):INDEX(A:A,21*(ROWS(A$1:A1))))