# Excel 2007 : Sumproduct across multiple Worksheets - #VALUE! Error

1. ## Sumproduct across multiple Worksheets - #VALUE! Error

I am working on a spreadsheet that supposed to sum across all my worksheets. Basically, I have a summary page and several other pages with montly records. On my monthly records I have Employee Names on the left and Serial Numbers on top. My summary page supposed to calculate how many hours each employee spend on each serial number. To get an idea of what I am doing please see attachement.

I used this formula; however, I get a #Value! error.

=SUMPRODUCT((INDIRECT("'"&\$A\$13:\$A\$14&"'!\$A\$2:\$A\$4"))=A2)*((INDIRECT("'"&\$A\$13:\$A\$14&"'!\$B\$1:\$C\$1"))=B1)*((INDIRECT("'"&\$A\$13:\$A\$14&"'!\$B\$2:\$C\$4")))

Could anybody help with this issue? Is there alternative formula that I could use??

Thanks!

Sample.xlsx

2. ## Re: Sumproduct across multiple Worksheets - #VALUE! Error

cant you just use this?....

=SUM(January:February!B2)

3. ## Re: Sumproduct across multiple Worksheets - #VALUE! Error

I mean I could; however, I am also working on a much bigger spreadsheet with 200+ employees and over 500 serial numbers spread over 20+ worksheets. So I was wondering if it's possible to create a formula that is able to calculate across multiple worksheets based on two conditions (employees and serial numbers). Any help is much appreciated.

4. ## Re: Sumproduct across multiple Worksheets - #VALUE! Error

If the names and serial numbers are all in the same sequence, the formula i showed above will give you what you need in a simple quick way...often, simlper is better lol. as far as multiple sheets are concerned, that is easy too. try this trick...

add a blank WS at the start and end of your existing sheets. name the 1st sheet Start and the last sheet End. then, when you construct your formulas, it will always be...=SUM(start:end!B2). this will also make it easy to add or remove sheets from within the range, just by draging them outside the start-end range.

If you are still dead-set on using the sumproduct, let me know and we can look again
good luck

5. ## Re: Sumproduct across multiple Worksheets - #VALUE! Error

I know what you mean, and I tried to apply your formula; however, I am having an issue with my two criterias (Employee Name and Serial Number). In other words, I want my summary page only to sum if the employee worked for that specific serial number. Additionally, if you open the attachment on my previous post you will notice that my sequence in January and February is not in order and somtimes duplicates. I tried all sorts of formulas, sumif, vlookup, used CRTL+SHIFT+ENTER, etc, but nothing worked so far. Furthermore, I made an experiement only using one worksheet. I applied the sumproduct formula above and everything worked fine. Nevertheless, as soon as I try to apply sumproduct on several sheets I get the #Value! error...

6. ## Re: Sumproduct across multiple Worksheets - #VALUE! Error

Hi,
May be consolidation with pivot table
Use wizard pivot table to add all your worsheets with one page per each month
Refresh pivot table when adding data in table
Note : serial numbers in summary are not corresponding to serial numbers in January and February in sample file
Hope this helps
Best regards

7. ## Re: Sumproduct across multiple Worksheets - #VALUE! Error

Thanks for all your answers... Somehow I am still having problmes applying your advices. To make things simpler I attached the actual spreadsheet I am working on.

Sample2.xlsx

Hope this will give you a better idea.

8. ## Re: Sumproduct across multiple Worksheets - #VALUE! Error

Hi,
See attached the summary in PT sheet done with the same procedure described above
Note : EE column is not taken into account
You can play with the several filters
Hope this is what you expect
Best regards

9. ## Re: Sumproduct across multiple Worksheets - #VALUE! Error

Thank you very much!!! This is more than I was looking for and will definately simplify my day. Thanks again!!!

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1