SOLVED

Help, Countif

Copper Contributor
I have a current formula of =countif(‘sheetx’!C2:C15,1) which works fine. In the next cell down in the same column I need the exact same formula but with a reference of C16:C29 (and then following down the column with the column always being C but the cells used being 30:43, 44:57 and so on (ie going up in 13’s)
When I drag the handle to try and auto fill from the current formula it defaults to the reference of C3:16.
How can I amend this as I have 30 rows which I need to input the formula?
2 Replies
best response confirmed by allyreckerman (Microsoft)
Solution

@lukegarratt81 

Let's say the first formula is in A2. Change it to

 

=COUNTIF(OFFSET('sheetx'!$C$2:$C$15, 14*(ROW(A2)-ROW($A$2)), 0), 1)

@Hans Vogelaar 

 

Amazing, thank you so much - really appreciated.

1 best response

Accepted Solutions
best response confirmed by allyreckerman (Microsoft)
Solution

@lukegarratt81 

Let's say the first formula is in A2. Change it to

 

=COUNTIF(OFFSET('sheetx'!$C$2:$C$15, 14*(ROW(A2)-ROW($A$2)), 0), 1)

View solution in original post