COUNTIF problems with data types

( I have looked extensively to see what issue I'm having but cannot find an adequate answer )

=COUNTIF(StartDate:StartDate, MONTH(@cell) = 2)

Task: Just count the rows where a Date Field is FEB ( we'll use StartDate as the field and it is a DATE field ) ----- The result I get is #INVALID DATA TYPE

Why is SS indicating a problem here?

https://app.smartsheet.com/b/publish?EQBCT=5da69588764148a1850fbebfe43d7e7b

Find more posts tagged with

Formulas

Comments

J. Craig Williams

This one has me stumped.

The error is coming from blank dates.

This is not how it was working previously. I'm investigating (but am not a Smarsheet employee)

Craig

Kent Vaughn

J. Craig - Thanks! Yes, I also concluded that if I excluded the blanks it seemed to work fine. I joke to my team, "I love SS, I hate SS". This weekend has been that example - trying to do what should be simple conditional logic ends up being an exercise in frusturation. Makes it hard to convince others to migrate solutions to SS.

-- Kent

Taylor F

Hello Kent,

Craig is correct by using the IFERROR. When the COUNTIF is checking the blank rows to see what month the cell is in, it will return a #INVALID DATA TYPE error. Placeing IFERROR around MONTH(@cell) and setting it to 0 will allow it to be skipped.

Taylor

J. Craig Williams

Kent,

Try this:

=COUNTIF(StartDate:StartDate, IFERROR(MONTH(@cell), 0) = 2)

My notes indicated this worked without the IFERROR() but I can not get it to again.