ISERROR or IFERROR .....Please help

Heath Sanders

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())))

J. Craig Williams

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()))