I'm using Excel 2007 on Windows 7.

I have 31 tabs (one for each day of the month), named 1 through 31, and a summary tab. On each daily tab there are multiple sets of data in columns Q through Y. column Q is always a person's name and the other columns are always numbers. Names will not always be in order and will not always appear in every tab. Rows 2 through 20 on each daily tab refer to dataset1, rows 21 through 50 refer to dataset2, and there are a total of 13 different datasets per tab.

I am looking to sum the value in column S within dataset1 across all tabs for each person, i.e., IFERROR(VLOOKUP,$Q2,'1'!$Q$2:$Q$20,3,FALSE),0)+IFERROR(VLOOKUP,$Q2,'2'!$Q$2:$Q$20,3,FALSE),0)+...+IFERROR(VLOOKUP,$Q2,'31'!$Q$2:$Q$20,3,FALSE),0)

I'm looking for a shorter formula. I would like to do this using without any add-ons, macros, etc. I tried first doing some google searching to try to find something that works, and came across the following result that seemed to work for other people:

=SUMPRODUCT(SUMIF(INDIRECT("'"&test&"'!$Q$2:$Q$20"),$Q2,INDIRECT("'"&test&"'!$S$3:$S$20")))

where test is a named range consisting of the numbers 1 through 31. I don't know if I translated the formula from the previous thread incorrectly, but I am getting a result of 0 even though the actual sum is >0. When I used the Evaluate Formula button in Excel, the issue seems to be where it went from

SUMPRODUCT(SUMIF(INDIRECT({"'1'!$Q$2:$Q$20";"'2'!$Q$2:$Q$20";...;"'31'!$Q$2:$Q$20"}),$Q2,INDIRECT("'"&test&"'!$S$3:$S$20")))

to

SUMPRODUCT(SUMIF({#VALUE!;#VALUE!;...;#VALUE!},$Q2,INDIRECT("'"&test&"'!$S$3:$S$20")))

The second result was an array formula using VLOOKUP, INDIRECT, INDEX, MATCH, and COUNTIF. I can't find that formula right now, but the result was similar.

Any ideas? What am I doing wrong?

Thanks,

wmc