How to automatically add new ranges to formulas in new rows?

  • Hi,


    So I am wanting to query a range in row with COUNT and COUNTIF queries. But I'm wanting to do this for one range in each row below, but the range reference is different in each rows.


    I have manually set each range (96 of them/96 rows), but how do I avoid having to manually write/copy/paste the range ref into every new set of formulas for each new row?


    In the picture below/attatched I am showing the formulas with the range set in upper rows. But I can't figure out how to automatically copy the range reference in the second coloumn into the COUNT/COUNTIF queries in the cells to its right.


    Any help would be so massively appreciated.


    Thanks


    Cor


  • Hello and Welcome to the Forum :)


    Sadly ... images are inert objects ... totally useless ...:thumbdown:


    Why don't you attach a sample file to illustrate precisely your objective ...;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Thanks for the quick reply. Fille attached.


    So, the source data I want to query is shaded yellow. For each row the range of the data I want to query varies. ie different coloumns. My issue is how do I/can I automatically generate a COUNT/COUNTIF query in each row which uses teh unique range I have set up for that row (Shaded Green)


    Normally I would just copy down, but I can't because the coloumns for the range change for each row.


    I hope that makes sense?


    Cor_test_file.xlsx

  • Thanks for your test file ...


    You request looks strange to me ... but attached is a proposal ...


    If it is not what you are looking for ... do not hesitate to post back ...


    :)

    Files

    • Test Cor.xlsx

      (11.92 kB, downloaded 8 times, last: )

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)