Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

ISERROR or IFERROR .....Please help

Options
Heath Sanders
edited 12/09/19 in Archived 2017 Posts

Hi Team,

 

Starting to expand my SUMIF formula which is great. The only issue is that it returns #INVALID DATA TYPE,  when the [Sold Date] cell is left blank.

 

I need to add a (ERROR formula in there somewhere im guessing. Im not sure excatly how ????

 

=SUMIFS(IFERROR([Value of Jobs]2........   How should it look please ?  Thank you

 

=SUMIFS([Value of Jobs]2:[Value of Jobs]50, Salesperson2:Salesperson50, "Heath Sanders", Status2:Status50, "WON", Result2:Result50, 1, [Sold Date]2:[Sold Date]50, MONTH(@cell) = MONTH(TODAY(), [Sold Date]2:[Sold Date]50, YEAR(@cell) = YEAR(TODAY())))

Comments

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭
    Options

    Heath,

     

    Oddly, I am NOT getting an #INVALID DATA TYPE when I have blanks in the [Sold Date] column.

     

    Your formula does have an error in it:

     

    =SUMIFS([Value of Jobs]2:[Value of Jobs]50, Salesperson2:Salesperson50, "Heath Sanders", Status2:Status50, "WON", Result2:Result50, 1, [Sold Date]2:[Sold Date]50, MONTH(@cell) = MONTH(TODAY(), [Sold Date]2:[Sold Date]50, YEAR(@cell) = YEAR(TODAY())))

     

    The last paranthesis should go here:

     

    ... MONTH(@cell) ) = MONTH( ...

     

    (I am VERY surprised that does not throw an error.

    You can see it gives an incorrect error by changing a Feb date to Feb 2016 date.)

     

    Can you post a screen shot of the error message with the offending data?

    Or share it will me. You have my email address.

     

    I would have thought that MONTH(@cell) would throw the error, but it does not appear to do so any longer.

     

    Craig

     

    UPDATE:

    The MONTH(@cell) throws an error when the cell is blank but has had data in it before. It does not throw an error when it is blank and has not had data. 

     

    Here's the new formula with IFERROR around the two @cell references.

    Note that if there is an error, the IFERROR returns a 0 - which won't match the MONTH or the YEAR.

     

    =SUMIFS([Value of Jobs]2:[Value of Jobs]50, Salesperson2:Salesperson50, "Heath Sanders", Status2:Status50, "WON", Result2:Result50, 1, [Sold Date]2:[Sold Date]50, IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), [Sold Date]2:[Sold Date]50, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))

This discussion has been closed.