# SUMIFS Formula and Blank Cells

Options
✭✭✭✭

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

• ✭✭✭✭
Options

Error Message #INVALID DATA TYPE

• ✭✭✭✭✭✭
Options

[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

• ✭✭✭✭
Options

Comes back with another 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")@cell

• ✭✭✭✭✭✭
edited 07/26/24
Options

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

• ✭✭✭✭
Options

Sorry but comes up with #Invalid Operation ?

• ✭✭✭✭✭✭
edited 07/26/24
Options

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

• ✭✭✭✭
Options

Perfect… you are a star. Thank you.

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭
Options

Good point… I will have a try.

• ✭✭✭✭
Options

Works as well… thank you for your help.

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!