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

wmc

New Member
Joined
Jan 2, 2012
Messages
22
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
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Robert Mika

MrExcel MVP
Joined
Jun 29, 2009
Messages
7,256
=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

New Member
Joined
Jan 2, 2012
Messages
22
Thanks Robert! It appears that fixed it. Glad it was that easy, and I feel a bit silly for not noticing it.
 

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
=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

Well-known Member
Joined
Dec 30, 2008
Messages
12,438
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
The formula appears to be well-formed, although $Q$2:$Q$20 and $S$3:$S$20 start at different rows...
 

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623

ADVERTISEMENT

=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

New Member
Joined
Jan 2, 2012
Messages
22
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

Well-known Member
Joined
May 9, 2009
Messages
16,623
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

New Member
Joined
Jan 2, 2012
Messages
22
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,473
Messages
5,624,933
Members
416,064
Latest member
PaulBr2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top