Forum Discussion
Formula help VLOOKUP? MATCH? INDEX>
In B2 on Sheet B:
=SUMIFS('Sheet A'!$C$2:$C$21,'Sheet A'!$A$2:$A$21,B$1,'Sheet A'!$B$2:$B$21,$A2)
Change Sheet A to the actual name of the first sheet, and adjust the ranges as needed.
Fill to the right, then down, or vice versa.
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