SOLVED

Help, Countif

%3CLINGO-SUB%20id%3D%22lingo-sub-2322645%22%20slang%3D%22en-US%22%3EHelp%2C%20Countif%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2322645%22%20slang%3D%22en-US%22%3EI%20have%20a%20current%20formula%20of%20%3Dcountif(%E2%80%98sheetx%E2%80%99!C2%3AC15%2C1)%20which%20works%20fine.%20In%20the%20next%20cell%20down%20in%20the%20same%20column%20I%20need%20the%20exact%20same%20formula%20but%20with%20a%20reference%20of%20C16%3AC29%20(and%20then%20following%20down%20the%20column%20with%20the%20column%20always%20being%20C%20but%20the%20cells%20used%20being%2030%3A43%2C%2044%3A57%20and%20so%20on%20(ie%20going%20up%20in%2013%E2%80%99s)%3CBR%20%2F%3EWhen%20I%20drag%20the%20handle%20to%20try%20and%20auto%20fill%20from%20the%20current%20formula%20it%20defaults%20to%20the%20reference%20of%20C3%3A16.%3CBR%20%2F%3EHow%20can%20I%20amend%20this%20as%20I%20have%2030%20rows%20which%20I%20need%20to%20input%20the%20formula%3F%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2322645%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2323166%22%20slang%3D%22en-US%22%3ERe%3A%20Help%2C%20Countif%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2323166%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1044896%22%20target%3D%22_blank%22%3E%40lukegarratt81%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet's%20say%20the%20first%20formula%20is%20in%20A2.%20Change%20it%20to%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DCOUNTIF(OFFSET('sheetx'!%24C%242%3A%24C%2415%2C%2014*(ROW(A2)-ROW(%24A%242))%2C%200)%2C%201)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2325125%22%20slang%3D%22en-US%22%3ERe%3A%20Help%2C%20Countif%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2325125%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAmazing%2C%20thank%20you%20so%20much%20-%20really%20appreciated.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New 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.