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

      • flexyourdata's avatar
        flexyourdata
        Iron Contributor
        Aug 01, 2023

        rkrastel 

         

        The most recent formula adjusts by 7 rows each time you copy down from C11. 

         

        Please could you either:

         

        a) add an example file showing the problem when using the formula I posted, or

        b) add screenshots showing the problem (please use =FORMULATEXT(C11) in D11 to show the formula in C11 and copy it down as well

         

        Thanks

Resources