Summing data based on multiple criteria - Dynamic formula option?

%3CLINGO-SUB%20id%3D%22lingo-sub-2233201%22%20slang%3D%22en-US%22%3ESumming%20data%20based%20on%20multiple%20criteria%20-%20Dynamic%20formula%20option%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2233201%22%20slang%3D%22en-US%22%3E%3CP%3EAttached%20is%20a%20two%20tab%20data%20set%20representative%20of%20a%20problem%20I%20run%20into%20frequently.%3C%2FP%3E%3CP%3E*%20The%20%22Payroll%20Source%22%20tab%20has%20financial%20information%20listed%20monthly%20in%20consecutive%20columns%20(i.e.%2012%20months%20%3D%2012%20columns).%3C%2FP%3E%3CP%3E*%20On%20the%20%22Payroll%20by%20division%22%20tab%2C%20I'm%20trying%20to%20break%20out%20the%20monthly%20data%20by%20Department%20and%20Division%2C%20ultimately%20needing%20to%20take%20what%20is%201%20column%20in%20the%20source%20data%20and%20%22allocate%22%20it%20across%20rows%2F%20columns%20based%20on%20Department%20%2F%20Division%20criteria%20in%20the%20respective%20month.%3C%2FP%3E%3CP%3E*%20The%20main%20thing%20I'm%20trying%20to%20solve%20for%20is%20how%20to%20create%20a%20dynamic%20formula%20that%20allows%20me%20to%20take%2012%20columns%20of%20monthly%20data%20from%20the%20source%20tab%20and%20%22spread%22%20it%20across%2048%20columns%20(4%20divisions%20per%20month)%20without%20having%20to%20change%20the%20sum%20range%20for%20each%20new%20month.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20currently%20populated%20the%20Payroll%20by%20Division%20tab%20using%20SUMIFS%20but%20that%20still%20requires%20me%20manually%20adjusting%2Fchanging%20the%20Sum%20Range%20within%20that%20formula%20whenever%20a%20new%20month%20starts.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20can%20I%20change%20this%20formula%20(or%20use%20a%20different%20one%20altogether%3F)%20that%20also%20checks%20the%20month%20on%20the%20source%20tab%20so%20that%20I%20could%20basically%20create%20the%20formula%20in%20Jan21%20of%20the%20%22Payroll%20by%20Division%22%20tab%20and%20drag%20across%20the%20rest%20of%20the%20months%20without%20having%20to%20manually%20change%20the%20Sum%20Range%20at%20the%20%22beginning%22%20of%20each%20new%20month%3F%20My%20first%20thought%20is%20to%20always%20add%20a%20third%20criteria%20-%20the%20month%20-%20but%20it's%20my%20understanding%20that%20the%20SUMIFS%20formula%20can't%20include%20row%20and%20column%20criteria%2C%20only%20one%20or%20the%20other.%20Is%20that%20correct%3F%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-2233201%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2233360%22%20slang%3D%22en-US%22%3ERe%3A%20Summing%20data%20based%20on%20multiple%20criteria%20-%20Dynamic%20formula%20option%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2233360%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F827443%22%20target%3D%22_blank%22%3E%40egspen2%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20D13%20on%20the%20Payroll%20by%20Division%20sheet%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DSUMPRODUCT('Payroll%20source'!%24G%2413%3A%24R%24167%2C('Payroll%20source'!%24D%2413%3A%24D%24167%3D%24C13)*('Payroll%20source'!%24E%2413%3A%24E%24167%3DD%2412)*('Payroll%20source'!%24G%243%3A%24R%243%3DINDEX(%2411%3A%2411%2C4*QUOTIENT(COLUMN(D%245)%2C4))))%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFill%20down%2C%20then%20to%20the%20right%20(or%20vice%20versa)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20BA13%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DSUMPRODUCT('Payroll%20source'!%24G%2413%3A%24R%24167%2C('Payroll%20source'!%24D%2413%3A%24D%24167%3D%24C13)*('Payroll%20source'!%24E%2413%3A%24E%24167%3DD%2412)*('Payroll%20source'!%24G%242%3A%24R%242%3DINDEX(%2411%3A%2411%2C4*QUOTIENT(COLUMN(BA%245)-1%2C4)%2B1)))%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFill%20down%2C%20then%20to%20the%20right%20(or%20vice%20versa)%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Attached is a two tab data set representative of a problem I run into frequently.

* The "Payroll Source" tab has financial information listed monthly in consecutive columns (i.e. 12 months = 12 columns).

* On the "Payroll by division" tab, I'm trying to break out the monthly data by Department and Division, ultimately needing to take what is 1 column in the source data and "allocate" it across rows/ columns based on Department / Division criteria in the respective month.

* The main thing I'm trying to solve for is how to create a dynamic formula that allows me to take 12 columns of monthly data from the source tab and "spread" it across 48 columns (4 divisions per month) without having to change the sum range for each new month.

 

I've currently populated the Payroll by Division tab using SUMIFS but that still requires me manually adjusting/changing the Sum Range within that formula whenever a new month starts.

 

How can I change this formula (or use a different one altogether?) that also checks the month on the source tab so that I could basically create the formula in Jan21 of the "Payroll by Division" tab and drag across the rest of the months without having to manually change the Sum Range at the "beginning" of each new month? My first thought is to always add a third criteria - the month - but it's my understanding that the SUMIFS formula can't include row and column criteria, only one or the other. Is that correct?

 

 

2 Replies

@egspen2 

In D13 on the Payroll by Division sheet:

 

=SUMPRODUCT('Payroll source'!$G$13:$R$167,('Payroll source'!$D$13:$D$167=$C13)*('Payroll source'!$E$13:$E$167=D$12)*('Payroll source'!$G$3:$R$3=INDEX($11:$11,4*QUOTIENT(COLUMN(D$5),4))))

 

Fill down, then to the right (or vice versa)

 

In BA13:

 

=SUMPRODUCT('Payroll source'!$G$13:$R$167,('Payroll source'!$D$13:$D$167=$C13)*('Payroll source'!$E$13:$E$167=D$12)*('Payroll source'!$G$2:$R$2=INDEX($11:$11,4*QUOTIENT(COLUMN(BA$5)-1,4)+1)))

 

Fill down, then to the right (or vice versa)

@egspen2 

I got as far as returning a 5x4 spilt range for each date.

= LET(
  source, XLOOKUP(@DateHdr,SourceDate,sourceData),
  SUMIFS(source,Department,DeptHdr,Division,DivHdr))

As things stand, the formula would need to by copied 12 times.  My goal would be to return the entire result as a single array formula but, even with the new Lambda functions, stitching the results together is likely to be a cumbersome process.