Forum Discussion

KKincaid's avatar
KKincaid
Copper Contributor
Feb 18, 2025

Relative cell reference in formula is not updating when dragging down rows

I have this formula: =SUM(COUNTIF(INDIRECT({"j16","n16","r16","v16","z16","ad16","ah16","al16","ap16","at16","ax16","bb16","bf16","bj16","bn16","br16","bv16","bz16","cd16","ch16","cl16","cp16","ct16","cx16","db16","df16","dj16","dn16","dr16","dv16","dz16","ed16","eh16","el16","ep16","et16","ex16"})|"=1"))

When I drag down to populate more rows with the formula, the cell references are not updating. It just copies the same formula. 

Have other formulas that update the cell references fine in the same sheet. I have tried adding $ in front of each cell, in between the row & cell #, removing quotes (which breaks the formula) nothing works. 

Any help is much appreciated.

Formulas and Functions
  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Feb 18, 2025

    Another option:

    =LET(
    vector, TAKE(WRAPROWS(J16:EX16, 4), , 1),
    SUM(N(vector = 1))
)

    I think any solution without INDIRECT is an upgrade here.

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Feb 18, 2025
    =SUMPRODUCT((J16:EX16=1)*(MOD(COLUMN(J:EX),4)=2))

    Not sure what you exactly want to do but this formula updates J16:EX16 to J17:EX17 if you fill it one row down. And the MOD(COLUMN(J:EX),4)=2 only includes columns J, N, R, V.... in the calculation. Can you share one of your formulas that update the cell references fine in the same sheet?

Resources