Jan 13 2021 01:15 PM
I have a series of rows that include criteria company, site, and dept. Each row has wages, vacation, and benefits. The data is on one tab and a summary on another tab. On the Master Summary tab I'm using a sumifs formula to add up the entries by company, site, and dept from the data tab but I'm using a hard code to reference the column from which to perform the sumif and I'm wondering if there is a way to use the range of the data on the data tab if I ID the column headers by the GL code??
This is the summary tab where all rows from the data tab have been summarized (it covers A7:F8):
This is from the data tab:
Here is the formula I've used to get the totals for 10000-10000-105B:
My concern is that if the columns ever get shifted around, the data comes from a third party, that referencing column D from the Data tab might be wrong if that column doesn't actually have the data for wages one payroll but gets changed to say Pay in Lieu.
Thanks for any help you can provide. It does work as written but I'd love to take the column reference out of the formula and try and sum across the data range.
Jan 13 2021 01:44 PM
In E2 on the Master Summary sheet:
=SUMIFS(INDEX(Data!$A:$ZZ,0,MATCH(E$1,Data!$1:$1,0)),Data!$AC:$AC,$A8,Data!$AD:$AD,$B8,Data!$AE:$AE,$D8)
Fill to the right to F2, then fill down.
Jan 14 2021 10:54 AM
@Hans Vogelaar Thank you, that worked perfectly and now I'm deploying it to a new group of workbooks!!!!