Sumifs, I think

%3CLINGO-SUB%20id%3D%22lingo-sub-2058469%22%20slang%3D%22en-US%22%3ESumifs%2C%20I%20think%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2058469%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20series%20of%20rows%20that%20include%20criteria%20company%2C%20site%2C%20and%20dept.%20Each%20row%20has%20wages%2C%20vacation%2C%20and%20benefits.%20The%20data%20is%20on%20one%20tab%20and%20a%20summary%20on%20another%20tab.%20On%20the%20Master%20Summary%20tab%20I'm%20using%20a%20sumifs%20formula%20to%20add%20up%20the%20entries%20by%20company%2C%20site%2C%20and%20dept%20from%20the%20data%20tab%20but%20I'm%20using%20a%20hard%20code%20to%20reference%20the%20column%20from%20which%20to%20perform%20the%20sumif%20and%20I'm%20wondering%20if%20there%20is%20a%20way%20to%20use%20the%20range%20of%20the%20data%20on%20the%20data%20tab%20if%20I%20ID%20the%20column%20headers%20by%20the%20GL%20code%3F%3F%3C%2FP%3E%3CP%3EThis%20is%20the%20summary%20tab%20where%20all%20rows%20from%20the%20data%20tab%20have%20been%20summarized%20(it%20covers%20A7%3AF8)%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22MarcusC1962_0-1610571786493.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F246231iAA8A7EA8316915CA%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22MarcusC1962_0-1610571786493.png%22%20alt%3D%22MarcusC1962_0-1610571786493.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20from%20the%20data%20tab%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22MarcusC1962_1-1610572030459.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F246232i25C0BBDAF57E4674%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22MarcusC1962_1-1610572030459.png%22%20alt%3D%22MarcusC1962_1-1610572030459.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EHere%20is%20the%20formula%20I've%20used%20to%20get%20the%20totals%20for%2010000-10000-105B%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22MarcusC1962_2-1610572303600.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F246233i447AE938ACFE675F%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22MarcusC1962_2-1610572303600.png%22%20alt%3D%22MarcusC1962_2-1610572303600.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20concern%20is%20that%20if%20the%20columns%20ever%20get%20shifted%20around%2C%20the%20data%20comes%20from%20a%20third%20party%2C%20that%20referencing%20column%20D%20from%20the%20Data%20tab%20might%20be%20wrong%20if%20that%20column%20doesn't%20actually%20have%20the%20data%20for%20wages%20one%20payroll%20but%20gets%20changed%20to%20say%20Pay%20in%20Lieu.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20any%20help%20you%20can%20provide.%20It%20does%20work%20as%20written%20but%20I'd%20love%20to%20take%20the%20column%20reference%20out%20of%20the%20formula%20and%20try%20and%20sum%20across%20the%20data%20range.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2058469%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2058495%22%20slang%3D%22en-US%22%3ERe%3A%20Sumifs%2C%20I%20think%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2058495%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F928236%22%20target%3D%22_blank%22%3E%40MarcusC1962%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20E2%20on%20the%20Master%20Summary%20sheet%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DSUMIFS(INDEX(Data!%24A%3A%24ZZ%2C0%2CMATCH(E%241%2CData!%241%3A%241%2C0))%2CData!%24AC%3A%24AC%2C%24A8%2CData!%24AD%3A%24AD%2C%24B8%2CData!%24AE%3A%24AE%2C%24D8)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFill%20to%20the%20right%20to%20F2%2C%20then%20fill%20down.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2061927%22%20slang%3D%22en-US%22%3ERe%3A%20Sumifs%2C%20I%20think%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2061927%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%20%26nbsp%3BThank%20you%2C%20that%20worked%20perfectly%20and%20now%20I'm%20deploying%20it%20to%20a%20new%20group%20of%20workbooks!!!!%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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): 

MarcusC1962_0-1610571786493.png

 

This is from the data tab:

MarcusC1962_1-1610572030459.png

Here is the formula I've used to get the totals for 10000-10000-105B:

MarcusC1962_2-1610572303600.png

 

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.

 

 

2 Replies

@MarcusC1962 

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.

@Hans Vogelaar   Thank you, that worked perfectly and now I'm deploying it to a new group of workbooks!!!!