+ Reply to Thread
Results 1 to 7 of 7

SUMIFS error with blank cells. Fix or workaround?

  1. #1
    Registered User
    Join Date
    05-01-2013
    Location
    Decatur, AL
    MS-Off Ver
    Excel 2008
    Posts
    4

    SUMIFS error with blank cells. Fix or workaround?

    I'm starting a new monthly task where I'll break down how much is owed (column G) for my loan office's accounts according to date made (column AE) and how far past due they are (column I). I need to set the past due ranges to be >= 30, 31 to 60, 61 to 90, and over 90 days.

    So, given the certain date at the end of the formula, I have this for >=30:
    =SUMIFS(G1:G10, I1:I10, "<=30", AE1:AE10, ">=1-1-13")

    this for 31 to 60
    =SUMIFS(G1:G10, I1:I10, ">30",B2:B10, "<=60", AE1:AE10, ">=1-1-13")

    this for 61 to 90
    =SUMIFS(G1:G10, I1:I10, ">60",B2:B10, "<=90", AE1:AE10, ">=1-1-13")

    & this for over 90:
    =SUMIFS(G1:G10, I1:I10, ">90", AE1:AE10, ">=1-1-13")

    These work fine except my range is always going to vary and I'll have to do this for each of our loan classes every month. So, it's impractical and prone to error for me to establish each individual range or fill each sheet with something like "blank" to avoid this error. I'd really like to just use this formula:
    =SUMIFS(G:G, I:I, "<=30", AE:AE, ">=1-1-13")
    ...and have Excel ignore empty cells. Is this possible? Is there another workaround?

    Thanks for any help!!

  2. #2
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: SUMIFS error with blank cells. Fix or workaround?

    Which range contains the empty cells that are causing the problem?

    This should work (works OK for me):

    =SUMIFS(G:G, I:I, "<=30", AE:AE, ">=1-1-13")
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Registered User
    Join Date
    05-01-2013
    Location
    Decatur, AL
    MS-Off Ver
    Excel 2008
    Posts
    4

    Re: SUMIFS error with blank cells. Fix or workaround?

    Really? That's odd. I get an error message if any range is greater than the number of cells in that column. So if I use:
    =SUMIFS(G1:G11, I1:I10, ">90", AE1:AE10, ">=1-1-13")

    but is blank after G10, then there's an error. I've tried different combinations, but so long as the range extends past where I have values, there's an error. I'm using Excel 2008 for Mac if that makes a difference.

  4. #4
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: SUMIFS error with blank cells. Fix or workaround?

    Quote Originally Posted by AshleyCooper View Post
    Really? That's odd. I get an error message if any range is greater than the number of cells in that column. So if I use:
    =SUMIFS(G1:G11, I1:I10, ">90", AE1:AE10, ">=1-1-13")
    Yes, that formula will generate an error because not all the ranges are the same length. You would have to do one or the other:

    =SUMIFS(G1:G10, I1:I10, ">90", AE1:AE10, ">=1-1-13")

    =SUMIFS(G1:G11, I1:I11, ">90", AE1:AE11, ">=1-1-13")

    But you don't have to worry about that if you use the entire columns:

    =SUMIFS(G:G, I:I, ">90", AE:AE, ">=1-1-13")

  5. #5
    Registered User
    Join Date
    05-01-2013
    Location
    Decatur, AL
    MS-Off Ver
    Excel 2008
    Posts
    4

    Re: SUMIFS error with blank cells. Fix or workaround?

    Sorry, I should have been more clear. Any combination, including if the ranges are all equal, will create the error if blank cells are present. For example:
    =SUMIFS(G1:G11, I1:I11, ">90", AE1:AE11, ">=1-1-13")
    will still create an error if there's no data after row 10. But, If I copy anything into row 11, like "blank", then the error disappears. Had to do a lot of trouble shooting to find this and when I noticed the problem, it did strike me as a crazy way to behave. IS this a glitch of some sort in my version of Excel?

  6. #6
    Registered User
    Join Date
    05-01-2013
    Location
    Decatur, AL
    MS-Off Ver
    Excel 2008
    Posts
    4

    Re: SUMIFS error with blank cells. Fix or workaround?

    Looks like it is a software glitch. Opened the file on a PC with Excel 2007 andthe formula worked fine with blank cells. How odd.

  7. #7
    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,936

    Re: SUMIFS error with blank cells. Fix or workaround?

    Quote Originally Posted by AshleyCooper View Post
    Looks like it is a software glitch. Opened the file on a PC with Excel 2007 andthe formula worked fine with blank cells. How odd.
    sooo which version where you using before? If it was before 2007, sumifS() will not work, it was only introduced in 2007 and is not backwards compatible
    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

+ 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. ERROR Handling on Pivot Table with Combo box workaround
    By hitxrafa in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-05-2012, 06:06 AM
  2. 2003 SUMIFS workaround.
    By blastronaut in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-25-2011, 07:48 PM
  3. [SOLVED] Nested If workaround - How does it work for other cells dynamicall
    By Rajula in forum Excel General
    Replies: 11
    Last Post: 04-21-2006, 11:55 AM
  4. busted workaround for 255 character limit in cells
    By mklapp in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-11-2005, 05:05 AM
  5. Please help, 1004 error no cells found workaround
    By baseballtheory in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-17-2005, 06:41 AM

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