Forum Discussion
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 range are not in table. I am wondering how i can use same formula but the data are in table in other sheet. I want to sum a data in column "Amount" from a table named Table2018. The data are in column J:K.Thanks
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.
9 Replies
check that file i hope help you how to do it
you can add sheets and make table name same way and will be report dynamic- Hi abdelaziz, thank you so much. The issue is solved. I did not lock the cell F4. Really appreciate your help
- you are welcome bro
BenjoBenjo If the oder of 'Parts' is the always the same in Both the summary and the yearly tables you can simply use
=INDIRECT("Table" & F4 & "[Amount]")This will spill the entire column 'Amount' from Table2019 into F5:F15 on Sheet1. Drag it across to fill the columns for the other years. No need to worry about where the tables sit in the workbook, as long as they are named consistently 'TableYYYY'.
If, on the other hand, the order of 'Parts' may be different in the summary and/or yearly tables, and building on your SUMIFS formula, you could use this:
=SUMIFS(INDIRECT("Table" & F$4 & "[Amount]"),INDIRECT("Table" & F$4 & "[Part]"),$E5)Copy down and across.
Riny_van_Eekelen Hi Riny, thanks for your time. I did try your way but there is still some issue. Showing a #REF error. Could you pls have a look.
BenjoBenjo Can't be sure without looking at the file itself, but are you sure that the table is called "Table2018"? And are the 'part' descriptions spelled exactly the same and the columns in the table are called Part and Amount exactly?. I.e. no trailing spaces or other invisible characters anywhere?
Attached a simplified mock-up of your example. It should work as explained.
