Forum Discussion
Summing data based on multiple criteria - Dynamic formula option?
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 colu...
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)