+ Reply to Thread
Results 1 to 5 of 5

Sumifs returning #value on closed workbook

  1. #1
    Registered User
    Join Date
    06-22-2013
    Location
    Bristol
    MS-Off Ver
    2010
    Posts
    7

    Sumifs returning #value on closed workbook

    Guys,

    Please can I have your assistance, =sumifs is returning #value as the workbook it's linked too is closed, I have read you can use sumproduct as an alternative but I don't understand it, here is my original formula

    =IF(SUMIFS('G:\GENERAL\Warehouse Team Files\2015\Performance\[Chill Receipts.xlsx]Week 1'!$L:$L,'G:\GENERAL\Warehouse Team Files\2015\Performance\[Chill Receipts.xlsx]Week 1'!$B:$B,$B448,'G:\GENERAL\Warehouse Team Files\2015\Performance\[Chill Receipts.xlsx]Week 1'!$Z:$Z,$A$446)=0,0,SUMIFS('G:\GENERAL\Warehouse Team Files\2015\Performance\[Chill Receipts.xlsx]Week 1'!$L:$L,'G:\GENERAL\Warehouse Team Files\2015\Performance\[Chill Receipts.xlsx]Week 1'!$B:$B,$B448,'G:\GENERAL\Warehouse Team Files\2015\Performance\[Chill Receipts.xlsx]Week 1'!$Z:$Z,$A$446))

    Can anyone convert the above to a sumproduct?

    Many thanks

    James

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Sumifs returning #value on closed workbook

    Hi, welcome to the forum

    I only did 1 side (removed the test for =0), try this...

    =SUMproduct(('G:\GENERAL\Warehouse Team Files\2015\Performance\[Chill Receipts.xlsx]Week 1'!$L:$L)*('G:\GENERAL\Warehouse Team Files\2015\Performance\[Chill Receipts.xlsx]Week 1'!$B:$B,$B448,'G:\GENERAL\Warehouse Team Files\2015\Performance\[Chill Receipts.xlsx]Week 1'!$Z:$Z=$A$446))

    If you still have a problem, upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.
    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
    Registered User
    Join Date
    06-22-2013
    Location
    Bristol
    MS-Off Ver
    2010
    Posts
    7

    Re: Sumifs returning #value on closed workbook

    FDibbins, I appreciate the reply, unfortunately I couldn't get your formula to work, I have tried to give you the most basic version of what I am trying to achieve to give you an idea of where we are.

    Chill Receipts.xlsx is an export of a report our in house system produces.

    Performance.xlsx is a 'team' of people where I would like to show their specific numbers.

    I hope I have explained my requirements

    Thanks again for your time

    Regards

    James
    Attached Files Attached Files

  4. #4
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Sumifs returning #value on closed workbook

    Hi.

    Not sure I understand the IF(0,0 clause (??), but:

    =SUMPRODUCT('G:\GENERAL\Warehouse Team Files\2015\Performance\[Chill Receipts.xlsx]Week 1'!$L$1:$L$5000,0+('G:\GENERAL\Warehouse Team Files\2015\Performance\[Chill Receipts.xlsx]Week 1'!$B$1:$B$5000=$B2),0+('G:\GENERAL\Warehouse Team Files\2015\Performance\[Chill Receipts.xlsx]Week 1'!$Z$1:$Z$5000=$A$1))

    is the equivalent you require.

    Note that, unlike with SUMIF(S), referencing more rows than necessary with SUMPRODUCT will have a detrimental effect upon performance (and certainly don't reference entire columns). I have chosen an upper bound of 5000, though obviously amend as appropriate (even better if you lower it).

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  5. #5
    Registered User
    Join Date
    06-22-2013
    Location
    Bristol
    MS-Off Ver
    2010
    Posts
    7

    Re: Sumifs returning #value on closed workbook

    Quote Originally Posted by XOR LX View Post
    Hi.

    Not sure I understand the IF(0,0 clause (??), but:

    =SUMPRODUCT('G:\GENERAL\Warehouse Team Files\2015\Performance\[Chill Receipts.xlsx]Week 1'!$L$1:$L$5000,0+('G:\GENERAL\Warehouse Team Files\2015\Performance\[Chill Receipts.xlsx]Week 1'!$B$1:$B$5000=$B2),0+('G:\GENERAL\Warehouse Team Files\2015\Performance\[Chill Receipts.xlsx]Week 1'!$Z$1:$Z$5000=$A$1))

    is the equivalent you require.

    Note that, unlike with SUMIF(S), referencing more rows than necessary with SUMPRODUCT will have a detrimental effect upon performance (and certainly don't reference entire columns). I have chosen an upper bound of 5000, though obviously amend as appropriate (even better if you lower it).

    Regards

    This worked absolutely perfectly so firstly thank you so much for your help. I would like to understand SUMPRODUCT, I just can't get my head around how it works. As I understand it multiplies all the numbers in a range with the numbers in another range, so how in my case does this match specific criteria in order to get the relevant results?

    I'm not sure why but it baffles me and without the basic understanding I can't work through the formula.

    Thanks again for your help.

    James

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Sumifs using vba in closed workbook
    By intex in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-16-2014, 04:16 PM
  2. Replies: 0
    Last Post: 03-27-2014, 12:38 PM
  3. [SOLVED] Help with SUMIFS with closed workbook
    By reem01 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 02-03-2014, 06:22 PM
  4. #VALUE errer received on a SUMIFS formula to a closed workbook
    By tom257 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-08-2013, 06:35 AM
  5. Replies: 2
    Last Post: 10-13-2009, 12:23 PM

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