SUMIFS Formula and Blank Cells
We have a formula which will value sales forecasts for this month, next month and month 2. It works fine when the sell value and date columns are all populated. But we have some sell values where the project date is not known and is blank. The formula does not work until we add a date or clear the sell value ?
=SUMIFS([Sell (£)]:[Sell (£)], [Project Date]:[Project Date], AND(MONTH(@cell ) = MONTH(TODAY()) + 1, YEAR(@cell ) = YEAR(TODAY())), Status:Status, "Quoted", Certainty:Certainty, ">0.2")@cell
Answers
-
Error Message #INVALID DATA TYPE
-
You need to add this criteria into your formula
[Project Date]:[Project Date], ISDATE(@cell),
You MONTH and YEAR functions are trying to turn a blank cell into date values, which is what's returning your INVALID DATA TYPE error. Including this criteria into your formula will omit any non-date values.
Jason Tarpinian - Sevan Technology
Smartsheet Aligned Partner
-
Ah! You've got a random @cell outside of your final parenthesis. Also @cell NEEDS to be lower case. @Cell will give you an UNPARSEABLE error.
=SUMIFS([Sell (£)]:[Sell (£)], [Project Date]:[Project Date], ISDATE(@cell ), AND(MONTH(@cell ) = MONTH(TODAY()), YEAR(@cell ) = YEAR(TODAY())), Status:Status, "Quoted", Certainty:Certainty, ">0.2")
Try this and you should be good to go.
Jason Tarpinian - Sevan Technology
Smartsheet Aligned Partner
-
Sorry but comes up with #Invalid Operation ?
-
Lesson learned. I need to build these formulas in an actual sheet instead of eyeballing them! 😁I built this one in a live sheet. We forgot to include the [Project Date]:[Project Date] range for the AND portion of the formula.
=SUMIFS([Sell (£)]:[Sell (£)], [Project Date]:[Project Date], ISDATE(@cell), [Project Date]:[Project Date], AND(MONTH(@cell) = MONTH(TODAY()) +1, YEAR(@cell) = YEAR(TODAY())), Status:Status, "Quoted", Certainty:Certainty, ">0.2")
Jason Tarpinian - Sevan Technology
Smartsheet Aligned Partner
-
Perfect… you are a star. Thank you.
-
I personally like to use the IFERROR function instead of repeating the range.
=SUMIFS([Sell (£)]:[Sell (£)], [Project Date]:[Project Date], AND(IFERROR(MONTH(@cell), 0) = MONTH(TODAY()) + 1, IFERROR(YEAR(@cell), 0) = YEAR(TODAY())), Status:Status, "Quoted", Certainty:Certainty, ">0.2")
Something to keep in mind though…
The above will not work to grab Jan 2025 when the current month is Dec 2024. 12 + 1 = 13, and there is no "month 13".
To accommodate this, you can pull the month from "next month" instead of adding 1 to the current month.
TODAY()
changes to
IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY()) + 1, 1, 1))
Which updates the whole formula to:
=SUMIFS([Sell (£)]:[Sell (£)], [Project Date]:[Project Date], AND(IFERROR(MONTH(@cell), 0) = MONTH(IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY()) + 1, 1, 1))), IFERROR(YEAR(@cell), 0) = YEAR(IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY()) + 1, 1, 1)))), Status:Status, "Quoted", Certainty:Certainty, ">0.2")
Then month +2 would look like this:
=SUMIFS([Sell (£)]:[Sell (£)], [Project Date]:[Project Date], AND(IFERROR(MONTH(@cell), 0) = MONTH(IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 2, 1), DATE(YEAR(TODAY()) + 1, MONTH(TODAY()) - 10, 1))), IFERROR(YEAR(@cell), 0) = YEAR(IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 2, 1), DATE(YEAR(TODAY()) + 1, MONTH(TODAY()) - 10, 1)))), Status:Status, "Quoted", Certainty:Certainty, ">0.2")
-
Good point… I will have a try.
-
Works as well… thank you for your help.
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!