Forum Discussion

rkrastel's avatar
rkrastel
Copper Contributor
Aug 01, 2023

Copying and Filling with Formula Logic

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 retai...
excel
flexyourdata's avatar
flexyourdata
Iron Contributor
Aug 01, 2023

rkrastel 

 

Try this:

 

=LET(
d,C11,
r,OFFSET(d,86+((ROW(d)-11)*6),0,1,24),
d=COUNTIF(r,1)
)
  • rkrastel's avatar
    rkrastel
    Copper Contributor
    Aug 01, 2023
    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's avatar
      flexyourdata
      Iron Contributor
      Aug 01, 2023

      rkrastel 

       

      I see. I misunderstood. I thought your calculation was comparing the content of C11 with the COUNTIF. 

       

      I see now that you want to put the COUNTIF in C11. 

       

      Try this instead:

       

      =COUNTIF(OFFSET(C97:Z97,(ROW()-11)*6,0),1)

       

      • rkrastel's avatar
        rkrastel
        Copper Contributor
        Aug 01, 2023

        flexyourdata 

         

        My mistake that I made the example unclear, I have edited it to [hopefully] clear up what I am looking to achieve

         

        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:

         

        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)

          

        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

         

        Hopefully this provides some additional clarity in what I would like to achieve

Resources