+ Reply to Thread
Results 1 to 9 of 9

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

  1. #1
    Forum Contributor
    Join Date
    04-11-2012
    Location
    Canada
    MS-Off Ver
    Excel 2007 & 2010
    Posts
    100

    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. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,059

    Re: Sumproduct across multiple Worksheets - #VALUE! Error

    cant you just use this?....

    =SUM(January:February!B2)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Contributor
    Join Date
    04-11-2012
    Location
    Canada
    MS-Off Ver
    Excel 2007 & 2010
    Posts
    100

    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. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,059

    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. #5
    Forum Contributor
    Join Date
    04-11-2012
    Location
    Canada
    MS-Off Ver
    Excel 2007 & 2010
    Posts
    100

    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. #6
    Forum Contributor
    Join Date
    02-22-2011
    Location
    Rhône Alpes, France
    MS-Off Ver
    Excel 2007
    Posts
    201

    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. #7
    Forum Contributor
    Join Date
    04-11-2012
    Location
    Canada
    MS-Off Ver
    Excel 2007 & 2010
    Posts
    100

    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. #8
    Forum Contributor
    Join Date
    02-22-2011
    Location
    Rhône Alpes, France
    MS-Off Ver
    Excel 2007
    Posts
    201

    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
    Attached Files Attached Files

  9. #9
    Forum Contributor
    Join Date
    04-11-2012
    Location
    Canada
    MS-Off Ver
    Excel 2007 & 2010
    Posts
    100

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

Bookmarks

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