Summing a specified value across multiple worksheets (VLOOKUP array? SUMPRODUCT/SUMIF?)

[wmc]

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

 

[Robert Mika]

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

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

Both ranges must be equal

 

[wmc]

Thanks Robert! It appears that fixed it. Glad it was that easy, and I feel a bit silly for not noticing it.

 

[T. Valko]

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

Any ideas? What am I doing wrong?

Thanks,
wmc

You have s light difference in the ranges otherwise that formula is syntactically correct.

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

If you expect a result >0 then there may be a problem with your data.

You may have unseen whitespace characters in the data causing the criteria to not match.

 

[jasonb75]

I'm not sure if the nature of the formula causes different behaviour, but the ranges in sumif don't have to be equal, the function resizes the range, but makes it volatile.

In your case, excel would change the range S3:S20 to S3:S21, so the results taken would be offset 1 row from the critera range.

Also from memory, the {#Value!; array when evaluating the formula is normal, but ultimately the formula still evaluates correctly.

 

[Aladin Akyurek]

The formula appears to be well-formed, although $Q$2:$Q$20 and $S$3:$S$20 start at different rows...

 

[T. Valko]

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

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

Both ranges must be equal

In general that is true.

However, in this application the ranges are within the SUMIF function which does not need to have equally sized ranges (although "best practices" would dictate so).

 

[wmc]

Also from memory, the {#Value!; array when evaluating the formula is normal, but ultimately the formula still evaluates correctly.

Thank you. I didn't realize this either. And thanks to all for replying so quickly. This is a great introduction to this board.

 

[T. Valko]

You have s light difference in the ranges otherwise that formula is syntactically correct.

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

If you expect a result >0 then there may be a problem with your data.

You may have unseen whitespace characters in the data causing the criteria to not match.

Another tidbit that you may be interested in...

Sometimes it may not be desirable to list all the sheet names in a range of cells. If the sheet names follow some sort of sequential naming pattern as yours do, then we can "build" the array of sheet names directly into the formula like this:

=SUMPRODUCT(SUMIF(INDIRECT("'"&ROW(INDIRECT("1:31"))&"'!$Q$2:$Q$20"),$Q2,INDIRECT("'"&ROW(INDIRECT("1:31"))&"'!$S$2:$S$20")))

Also, we can use a named expression to replace the bit that builds the sheet names and end up with something that looks like your original formula without having to list the sheet names in a range of cells.

Defined name
Name: Sheets
Refers to: =ROW(INDIRECT("1:31"))

Then, your formula becomes:

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

And here's yet another tidbit...

Any range references that are quoted within the INDIRECT function will automatically be evaluated as absolute references so we can get rid of some of the dollar signs $. So, the formula now becomes:

=SUMPRODUCT(SUMIF(INDIRECT("'"&Sheets&"'!Q2:Q20"),$Q2,INDIRECT("'"&Sheets&"'!S2:S20")))

Now, that looks really sweet!

 

[wmc]

Thank you Biff. I am always interested in additional tidbits. Incidentally, my actual formula doesn't have the $Q$2:$Q$20 and $S$2:$S$20 in there. I'm using the same formula for most of my datasets, so the ranges are variable. I'm using another formula altogether to generate the proper ranges according to the dataset I need the sum for.