Using Excel to SUM values from multiple sheets using a date value as a lookup

0
votes

I have the following situation for a payroll spreadsheet I am working on

  1. I have multiple sheets with the exact same layout. One for each employee
  2. Each row of the employee sheet has a Pay Period End Date and other values like year to date gross pay, net pay, taxes witheld and other values
  3. Since more employees can be added throughout the year I cannot just list out each employee in a formula

On a separate sheet, called Quarterly Report, I am trying to SUM the values from all of the employee sheet based on a specific date which is listed in a cell.

How do I without declaring every sheet separately, in a formula, lookup the row defined by the date on Quarterly Report and SUM the Year-to-date gross pay for that row on every sheet.

I.E. SUM('Bob:Susan'!C1) but the row is determined by a VLOOKUP or a Match or something along those lines.

I have been trying to do this with VLOOKUP, INDEX/MATCH, and SUMIF. I am not sure if I am doing something wrong in these formulas or if I am just using the wrong formula. This is one of my attempts

=SUMIF('Susan:Employee Template'!B13:B38,B3,'Susan:Employee Template'!M13:M38)
1
excel-formulaexcel-2010

1 Answers

0
votes

List all your sheet names in a range on the sheet and name the range something like SheetNames Then yo can use:

=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetNames&"'!"&"B13:B38"),B3,INDIRECT("'"&SheetNames&"'!"&"M13:M38")))