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

#### wmc

##### New Member
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

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
=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
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
=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

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.

##### MrExcel MVP
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

=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
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
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"))

=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
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.

Replies
2
Views
42
Replies
3
Views
191
Replies
1
Views
217
Replies
3
Views
155
Replies
1
Views
100

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.

### Which adblocker are you using?

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

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