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
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
-
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()))
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives