I am trying to write a excel formula that will calculate a data set based off of the column header for an area. Essentially, I am using it to help automate some of the mundane reconciliations that I do.
The data: I am wanting to search the column header for "Car_ERN" and sum that entire column. My formula I have been trying to get work is in two versions.
One version is using sumifs:
SUMIFS('Sum Payroll Dept Dump'!$Y$2:$CG$85,'Sum Payroll Dept Dump'!$Y$1:$CG$1,"ERN_CAR_AMT")
The other version is incorporating Sumproduct but I get a zero return. The "2" is searching a column that has multiple 2s in it because I want all rows for this criteria.
`SUMPRODUCT('Sum Payroll Dept Dump'!$Y$2:$CG$200,('Sum Payroll Dept Dump'!$Y$1:$CG$1='Filter Sheet'!A8)*('Sum Payroll Dept Dump'!A2:A200="2"))`
If there is a better way to do it without macros that would be much appreciated.
Thanks for the help!
Formula only solution, assuming your desired column header to look for is in sheet 'Filter Sheet' cell A8 (using your example, that cell would contain "ERN_CAR_AMT"):
=SUMIF('Sum Payroll Dept Dump'!$A:$A,2,INDEX('Sum Payroll Dept Dump'!$Y:$CG,0,MATCH('Filter Sheet'!A8,'Sum Payroll Dept Dump'!$Y$1:$CG$1,0)))