This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
I need to use the COUNTIF formula on every 40 rows. For example, =COUNTIF(J2:J41,"Yes"). I need this pattern to autofill into 3,000+ cells as follows:
=COUNTIF(J2:J41,"Yes")
=COUNTIF(J42:J81,"Yes")
=COUNTIF(J82:J121,"Yes")
When I try to autofill the formula into the rest of the cells in my column, it autofills in this way, instead of my intended pattern:
=COUNTIF(J3:J42,"Yes")
=COUNTIF(J4:J43,"Yes")
I have tried manually entering the formula into 4 cells, highlighting those cells, and autofilling downwards, however, it still does not recognize my pattern.
I need this formula in 3,000+ cells, so manual entry is not an option.
My Excel Version: Microsoft® Excel® for Microsoft 365 MSO (Version 2202 Build 16.0.14931.20648) 32-bit
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
Answer accepted by question author
Let's say the first formula is in a cell in row 2. Change it to
=COUNTIF(INDEX(J:J, 40*(ROW(A2)-ROW(A$2))+2):INDEX(J:J, 40*(ROW(A2)-ROW(A$2))+41), "Yes")
Fill down.
This worked. Thanks so much!