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

W

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
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Sort by date Sort by votes
=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
 
Upvote 0
Thanks Robert! It appears that fixed it. Glad it was that easy, and I feel a bit silly for not noticing it.
 
Upvote 0
wmc said:
=SUMPRODUCT(SUMIF(INDIRECT("'"&test&"'!$Q$2:$Q$20"),$Q2,INDIRECT("'"&test&"'!$S$3:$S$20")))

Any ideas? What am I doing wrong?

Thanks,
wmc
Click to expand...
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.
 
Upvote 0
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.
 
Upvote 0
Robert Mika said:
=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
Click to expand...
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).
 
Upvote 0
jasonb75 said:
Also from memory, the {#Value!; array when evaluating the formula is normal, but ultimately the formula still evaluates correctly.
Click to expand...

Thank you. I didn't realize this either. And thanks to all for replying so quickly. This is a great introduction to this board.
 
Upvote 0
T. Valko said:
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.
Click to expand...
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! :)
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question Question
Vlookup formula across multiple tables #N/A when lookup not present
Replies
3
Views
119
Beh162
B
R
  • Question Question
Sumifs with nested Xlookup-- Adding Indirect to perform calculation across multiple sheets
Replies
1
Views
668
Eric W
Eric W
D
  • Question Question
If formula with date & VLOOKUP
Replies
1
Views
283
etaf
etaf
P
  • Question Question
Populating, aggregating and sorting multiple matching values in Excel
Replies
0
Views
199
Pbobas
P
A
  • Solved
SUMPRODUCT Help
Replies
5
Views
135
AYouQueTai
A

Forum statistics

Threads
1,223,993
Messages
6,175,842
Members
452,675
Latest member
duongtruc1610

Share this page

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
Back
Top