Using Excel to SUM values from multiple sheets using a date value as a lookup -
i have following situation payroll spreadsheet working on
- i have multiple sheets exact same layout. 1 each employee
- each row of employee sheet has pay period end date , other values year date gross pay, net pay, taxes witheld , other values
- since more employees can added throughout year cannot list out each employee in formula
on separate sheet, called quarterly report, trying sum values of employee sheet based on specific date listed in cell.
how without declaring every sheet separately, in formula, lookup row defined date on quarterly report , sum year-to-date gross pay row on every sheet.
i.e. sum('bob:susan'!c1) row determined vlookup or match or along lines.
i have been trying vlookup, index/match, , sumif. not sure if doing wrong in these formulas or if using wrong formula. 1 of attempts
=sumif('susan:employee template'!b13:b38,b3,'susan:employee template'!m13:m38)
list sheet names in range on sheet , name range sheetnames
yo can use:
=sumproduct(sumif(indirect("'"&sheetnames&"'!"&"b13:b38"),b3,indirect("'"&sheetnames&"'!"&"m13:m38")))
Comments
Post a Comment