+ Reply to Thread
Results 1 to 6 of 6

#ref error with sumifs

  1. #1
    Forum Contributor
    Join Date
    03-08-2011
    Location
    Lubbock, TX, USA
    MS-Off Ver
    MS OFFICE 365 EXCEL, OUTLOOK, WORD, POWERPOINT
    Posts
    194

    #ref error with sumifs

    I don't understand why I keep getting #REF error in a column of sumifs.

    I consolidate several spreadsheets into one. I delete the unneeded information. I save the spreadsheet. I always save to the same location with the same name so it over writes the existing sheet.

    On another spreadsheet I have a column that sumifs from that first cleaned up data workbook. But, I have to redo it every time. It opens with a #Ref error and I get to redo the sumifs. I know I could save a snip of the formula and just copy it down the column again. I don't like that because other people might find the method useful and as it is it is clunky.

    Any ideas?

  2. #2
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: #ref error with sumifs

    Attach a sample workbook showing the error.

  3. #3
    Forum Expert
    Join Date
    10-02-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    1,222

    Re: #ref error with sumifs

    Quote Originally Posted by Mr_Phil View Post
    I delete the unneeded information
    This is your issue. The #REF error means the reference in the formula no longer exists, Excel can not resolve it/figure it out.

    As a simple example, you put a number in cell A1, in B1 you enter a formula =SUM(A1). Then highlight the column A and delete it, your formula now has a reference error as what used to be A1 no longer exists. Yes the sheet has an A1 (that contains the formula you wrote, essentially it used to be B1) but its not the same A1 your formula referenced and since there is nothing to the left of column A for Excel to adjust to it results in an error when A was deleted.

    I find this the most likely issue you are facing. Any time you delete a cell/column/row you run the risk of breaking references.

    By extension the same is possible when using named ranges. They can either calculate to an invalid reference and cause the error or like a formula have referenced something that was deleted and no longer is valid. If a formula references a named range with a REF error you are likely to get a ref error in the formula too.

    Hard to say what the fix is, if any, without seeing samples
    Ways to get help: Post clear questions, explain your overall goal, supply as much background as possible, respond to questions asked of you by those trying to help and post sample(s) files.

    "I am here to help, not do it for people" -Me

  4. #4
    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: #ref error with sumifs

    I delete the unneeded information
    As Zer0Cool suggests, that is probably the cause.

    Did you just delete the cell contents (shouldnt cause a problem) or did you delete entire rows/columns (WILL cause a problem if they were included as a reference in the formula)?
    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

  5. #5
    Forum Contributor
    Join Date
    03-08-2011
    Location
    Lubbock, TX, USA
    MS-Off Ver
    MS OFFICE 365 EXCEL, OUTLOOK, WORD, POWERPOINT
    Posts
    194

    Re: #ref error with sumifs

    Quote Originally Posted by Zer0Cool View Post
    This is your issue. The #REF error means the reference in the formula no longer exists, Excel can not resolve it/figure it out.
    Quote Originally Posted by FDibbins View Post
    Did you just delete the cell contents (shouldnt cause a problem) or did you delete entire rows/columns (WILL cause a problem if they were included as a reference in the formula)?
    Gents, I forgot to respond back when this was answered. But, I made it a point to check into this so I would understand what was going on. Your pointers were proven true and allowed me to do a workover that avoids the issue. Thank you so much to helping me out.

  6. #6
    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: #ref error with sumifs

    Happy to help, thanks for the feedback

+ 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 Value! Error
    By Ubayd94 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-23-2017, 09:26 AM
  2. SUMIFS <> error???
    By AstToTheRegionalMGR in forum Excel General
    Replies: 6
    Last Post: 03-25-2015, 11:30 AM
  3. SUMIFS Error
    By xourico24 in forum Excel General
    Replies: 8
    Last Post: 11-10-2014, 08:56 AM
  4. [SOLVED] SUMIFS error
    By strud in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 08-29-2014, 10:21 AM
  5. [SOLVED] SUMIFS error= #VALUE!
    By z_eeen in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-12-2014, 02:07 PM
  6. [SOLVED] If error(sumifs......
    By shiftyspina in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 07-16-2013, 02:13 PM
  7. SUMIFS() error?
    By fgrose in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-07-2006, 01:45 PM

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