Forum Discussion

BenjoBenjo's avatar
BenjoBenjo
Copper Contributor
Oct 24, 2023
Solved

SUMIFS with INDIRECT

Hi, I am trying to reference a a column in a table using sumifs indirect reference. I was able to use this formula =SUMIFS(INDIRECT("'"&F$4&"'!$K:$K"),INDIRECT("'"&F$4&"'!$J:$J"),$E5) but the data...
excel
Formulas and Functions
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Oct 25, 2023

    BenjoBenjo The file link requires a log-in so I can't access it. Attaching another file with three years and the same formula copied across. It works as intended.

BenjoBenjo's avatar
BenjoBenjo
Copper Contributor
Oct 25, 2023

Riny_van_Eekelen Hi Riny, thanks so much for your help. Using your sample it worked for only th 2018. When i dragged the formula across for 2019 and 2020, it still gives me issue. I noticed that even the table name is "Table2018" I can use just "Table". I was with the notion that i have to use the exact table name "Table2018" which i was wrong and now it makes sense that it wont work when i drag across. However, with your formula it should work for 2019 and 2020 as well. I attached my workings here https://docs.google.com/spreadsheets/d/1zC4KShgURZASj6dFB6XVYe03MPhsP7nv/edit?usp=drive_link&ouid=107977624639648515691&rtpof=true&sd=true

I would really appreciate if you could kindly help me on this.

 

Thanks  

Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Oct 25, 2023

BenjoBenjo The file link requires a log-in so I can't access it. Attaching another file with three years and the same formula copied across. It works as intended.

  • BenjoBenjo's avatar
    BenjoBenjo
    Copper Contributor
    Oct 25, 2023

    Riny_van_Eekelen Thanks heaps. Now its making sense. The issue was I did not lock the cell F4. I really appreciate your help on this. 👍👍👍👍👍

Resources