Forum Discussion
Formula help VLOOKUP? MATCH? INDEX>
HansVogelaar sorry another problem. The data I am referring to in Sheet A is in a data table. When I point to the table in Sheet A, I get the correct values, but the columns are relative references so I cannot copy them across in Sheet B. If I try to create a reference using 'Sheet A'!$b$2:$b$109, the sumif does not find the value. This is my first time using data tables outside of the classroom so I am wondering if there is some trick to referring to table references absolutely. Or do I need to take the data out of table format?
The formula does indeed not work correctly if you use structured table references, but as far as I can tell it should work OK if you use range references even if the data on Sheet A are in a table.
Could you attach a sample workbook without sensitive/proprietary data?
HansVogelaar here is a sample - Sheet 2, Column Z has an example of what I want to see and be able to copy across to other dates. Sheet A currently has just one year and location but I think that is enough to solve the problem with.
This should work in Z3:
=SUMIFS(_2019__7[[Column2]:[Column2]],_2019__7[[Column1]:[Column1]],$A3,_2019__7[[Column3]:[Column3]],Z$1)
This can be filled to the right. See the attached version.
The idea is from Absolute Structured References in Excel Table Formulas
HansVogelaar thanks again for your help!! It works great!