Forum Discussion

rkrastel's avatar
rkrastel
Copper Contributor
Aug 08, 2023
Solved

Dragging Indirect Formula Down

Hello,

 

I am looking to copy/drag down the following formula (which is housed in C77):

=COUNTIF(E77:(INDIRECT($C$75&77)),1)/COUNT(E77:(INDIRECT($C$75&77)))

Example for each row:
=COUNTIF(E77:(INDIRECT($C$75&77)),1)/COUNT(E77:(INDIRECT($C$75&77)))
=COUNTIF(E77:(INDIRECT($C$75&78)),1)/COUNT(E77:(INDIRECT($C$75&78)))
=COUNTIF(E77:(INDIRECT($C$75&79)),1)/COUNT(E77:(INDIRECT($C$75&79)))
etc..

 

The intended result is that I would like to be able to specify a specific Column as part of C75 (lets say "G") and have that apply to all of the respective rows, but first I need to populate each row with this formula so that it applies. The problem is that when I try to drag/copy down the formula, "77" remains unchanged (would expect it to increase with each row: 77, 78, 79, etc).

 

Any help or recommendations are greatly appreciated!

  • rkrastel Try it like this:

    =COUNTIF(E77:INDIRECT($C$75&ROW(A77)),1)/COUNT(E77:INDIRECT($C$75&ROW(A77)))

     

     

    This one can be dragged down to reference row 78, 79 etc. 

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor

    rkrastel Try it like this:

    =COUNTIF(E77:INDIRECT($C$75&ROW(A77)),1)/COUNT(E77:INDIRECT($C$75&ROW(A77)))

     

     

    This one can be dragged down to reference row 78, 79 etc. 

Resources