How to count dates in a column? INVALID DATATYPE error


I want to count how many dates in a column called "Completed" occur in the month of January.

Using this formula

=COUNTIF([Completed]:[Completed], MONTH(@cell) = 1)

returns INVALID DATATYPE whether I use it in a cell on the sheet or in a sheet summary field or on a separate metric sheet with the formula referencing the Completed column in the data sheet.

The explanation of the error is as follows: The formula contains or references an incompatible data type, such as =INT("Hello")

The column I reference is set up as a date column and the column where the formula is is a text/# column. I am unclear where/why the problem is occurring. I feel like it must be something obvious/simple but I am stumped.

I appreciate your insights, Smartsheet Community!




Best Answer

  • DKazatsky2
    DKazatsky2 ✭✭✭✭✭✭
    edited 01/26/24 Answer ✓

    @Carroll Wall

    IFERROR isn't what you want in this situation. Try the following, it should exclude any rows that don't have a valid date, which includes blanks.

    =COUNTIFS(Completed:Completed, ISDATE(@cell), Completed:Completed, MONTH(@cell) = 1)


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!