SUMIFS Formula and Blank Cells

Rob Pritchard

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

Rob Pritchard

Error Message #INVALID DATA TYPE

Jason Tarpinian

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.

Rob Pritchard

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

Jason Tarpinian

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.

Rob Pritchard

Sorry but comes up with #Invalid Operation ?

Jason Tarpinian

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

Rob Pritchard

Perfect… you are a star. Thank you.

Paul Newcome

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

Rob Pritchard

Good point… I will have a try.

Rob Pritchard

Works as well… thank you for your help.

Paul Newcome

Happy to help. 👍️