Hello. Please bear with me as I am new to this forum. I will do my best to make my problem and the steps I've already taken as clear as I can.
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
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
