Copying and Filling with Formula Logic

Question

Hi everyone, I am currently having a lot of difficulty in understanding how I may achieve this. As indicated in the title, I have a specific formula that I would like to have copied down and to retain the logic of that formula&nbsp;Ideally, I would be able to copy the logic as follows (maintaining the same column, but adding +7 for the Row value) from C11 down:&nbsp;C11: "=COUNTIF('Sheet_1'!C97:Z97,1)"
C12: "=COUNTIF('Sheet_1'!C104:Z104,1)"
C13: "=COUNTIF('Sheet_1'!C111:Z111,1)"
etc..&nbsp;What would the best approach be to tackle this scenario?&nbsp;

flexyourdata · Answer

rkrastel&nbsp;&nbsp;Try this:&nbsp;=LET(
d,C11,
r,OFFSET(d,86+((ROW(d)-11)*6),0,1,24),
d=COUNTIF(r,1)
)

rkrastel · Answer

Hi, thank you for your suggestion! Unfortunately with this formula, I am receiving a "TRUE" statement when I would instead be looking to receive the corresponding COUNTIF calculation.

Just for clarity/visibility, I added the formula you suggested in C12

flexyourdata · Answer

rkrastel&nbsp;&nbsp;I see. I misunderstood. I thought your calculation was comparing the content of C11 with the COUNTIF.&nbsp;&nbsp;I see now that you want to put the COUNTIF in C11.&nbsp;&nbsp;Try this instead:&nbsp;=COUNTIF(OFFSET(C97:Z97,(ROW()-11)*6,0),1)&nbsp;

rkrastel · Answer

flexyourdata&nbsp;&nbsp;My mistake that I made the example unclear, I have edited it to [hopefully] clear up what I am looking to achieve&nbsp;I have tried this adjusted formula, but it does not appear to be calculating what I need. To further clarify, I would like each subsequent row to automatically apply the same logic when performing the calculation as defined in the example (where the only change in the formula is the Row # +7) and be able to copy down. Breakdown as such:&nbsp;What I would like to happen when copying + pasting the formula down:C11: "=COUNTIF('Sheet_1'!C97:Z97,1)"
C12: "=COUNTIF('Sheet_1'!C104:Z104,1)"
C13: "=COUNTIF('Sheet_1'!C111:Z111,1)"

*The cell value is the only variable that is changing (+7 with each row)&nbsp;&nbsp;What is actually happening when copying + pasting the formula down:C11: "=COUNTIF('Sheet_1'!C97:Z97,1)"
C12: "=COUNTIF('Sheet_1'!C98:Z98,1)"
C13: "=COUNTIF('Sheet_1'!C99:Z99,1)"

*The cell value is only incremented by +1 when I would like it to be +7 each time&nbsp;Hopefully this provides some additional clarity in what I would like to achieve

flexyourdata · Answer

rkrastel&nbsp;&nbsp;The most recent formula adjusts by 7 rows each time you copy down from C11.&nbsp;&nbsp;Please could you either:&nbsp;a) add an example file showing the problem when using the formula I posted, orb) add screenshots showing the problem (please use =FORMULATEXT(C11) in D11 to show the formula in C11 and copy it down as well&nbsp;Thanks

Forum Discussion

Copying and Filling with Formula Logic

5 Replies

Resources