+ Reply to Thread
Results 1 to 13 of 13

Do linked Files have to be open Using SUMIF Formula?

  1. #1
    Registered User
    Join Date
    03-30-2012
    Location
    new york,ny
    MS-Off Ver
    Excel 2003
    Posts
    14

    Do linked Files have to be open Using SUMIF Formula?

    I am having a problem using the SUMIF formula. Everything works great when all the files I am using are open. As soon as I close the files that are in my formula, the cell changes to #VALUE

    This is the formula:

    =SUMIF('P:\folder\2012\[timesheet.xls]Sheet1!$A$3:$A$34,$A10000,'P:\folder\2012\[timesheet.xls]Sheet1!$J$3:$J$34)

    Any ideas?

  2. #2
    Valued Forum Contributor
    Join Date
    03-14-2012
    Location
    Arizona USA
    MS-Off Ver
    Excel 2000/2007
    Posts
    408

    Re: Do linked Files have to be open Using SUMIF Formula?

    With linked values,
    The destination file is only updated when it is opened. The source file does not have to be open. Excel(in the background) will open the linked file, pull the values it needs, put them where they belong and close the source file.

  3. #3
    Registered User
    Join Date
    03-30-2012
    Location
    new york,ny
    MS-Off Ver
    Excel 2003
    Posts
    14

    Re: Do linked Files have to be open Using SUMIF Formula?

    That is what I thought but every time I close the source file the cell with the formula changes to #VALUE if I put the curser in the formula bar and hit enter. So strange.

  4. #4
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Do linked Files have to be open Using SUMIF Formula?

    SUMIF (and COUNTIF) will not work with a closed source file. Try SUMPRODUCT

    =SUMPRODUCT(--('P:\folder\2012\[timesheet.xls]Sheet1!$A$3:$A$34=$A10000),'P:\folder\2012\[timesheet.xls]Sheet1!$J$3:$J$34)

  5. #5
    Valued Forum Contributor
    Join Date
    03-14-2012
    Location
    Arizona USA
    MS-Off Ver
    Excel 2000/2007
    Posts
    408

    Re: Do linked Files have to be open Using SUMIF Formula?

    After you close the source file, close the destination file and reopen the destination file. See what happens.

  6. #6
    Registered User
    Join Date
    03-30-2012
    Location
    new york,ny
    MS-Off Ver
    Excel 2003
    Posts
    14

    Re: Do linked Files have to be open Using SUMIF Formula?

    Hi Dennis, If I close the source and then the destination and reopen the destination it looks like it is fine. BUT then if I filter the column I get the #VALUE again.

    Could there be something I need to change in the excel settings?

  7. #7
    Registered User
    Join Date
    03-30-2012
    Location
    new york,ny
    MS-Off Ver
    Excel 2003
    Posts
    14

    Re: Do linked Files have to be open Using SUMIF Formula?

    Quote Originally Posted by JosephP View Post
    SUMIF (and COUNTIF) will not work with a closed source file. Try SUMPRODUCT

    =SUMPRODUCT(--('P:\folder\2012\[timesheet.xls]Sheet1!$A$3:$A$34=$A10000),'P:\folder\2012\[timesheet.xls]Sheet1!$J$3:$J$34)
    I tried SUMPRODUCT but it didn't work, the calculation kept giving me a 0 when it should have been 3.5

  8. #8
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Do linked Files have to be open Using SUMIF Formula?

    Then you probably have a type mismatch. Countif will treat anything that looks like a number as a number, but SUMPRODUCT will treat a number and a text representation of that number as different. If the source won't be open, you will have to make SUMPRODUCT work - by converting all values to text, or all to numbers. You can make the formula do the conversion as long as each data set is consistent.

  9. #9
    Valued Forum Contributor
    Join Date
    03-14-2012
    Location
    Arizona USA
    MS-Off Ver
    Excel 2000/2007
    Posts
    408

    Re: Do linked Files have to be open Using SUMIF Formula?

    I agree with JosephP on the type mismatch issue.

  10. #10
    Registered User
    Join Date
    03-30-2012
    Location
    new york,ny
    MS-Off Ver
    Excel 2003
    Posts
    14

    Re: Do linked Files have to be open Using SUMIF Formula?

    Thanks for all the help. Unfortunatly, still doesn't work. All values are numbers and still get a result of 0

  11. #11
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Do linked Files have to be open Using SUMIF Formula?

    That sounds very unlikely. They may look like numbers but some are actually stored as text. It would save time if you posted sample files.

  12. #12
    Registered User
    Join Date
    03-30-2012
    Location
    new york,ny
    MS-Off Ver
    Excel 2003
    Posts
    14

    Re: Do linked Files have to be open Using SUMIF Formula?

    I've attached sample files. "2012 hours per job" is the destination file and "test file" is the source file. Let me know what you think.

    Thank you!!
    Attached Files Attached Files

  13. #13
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Do linked Files have to be open Using SUMIF Formula?

    You did not enter the formula the way I said. It has to be

    =SUMPRODUCT(--('[test file.xlsx]Sheet1'!$A$3:$A$34=$A10000),'[test file.xlsx]Sheet1'!$J$3:$J$34)

    and not just

    =SUMPRODUCT('[test file.xlsx]Sheet1'!$A$3:$A$34=$A10000,'[test file.xlsx]Sheet1'!$J$3:$J$34)


    for information here is a microsoft article on the closed workbook issue since I get the impression that Dennis is unconvinced: http://support.microsoft.com/kb/260415
    Last edited by JosephP; 04-05-2012 at 05:36 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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