COUNTIFS function showing error

I am counting the nos. of specific activity within the specific month. (Two different columns)

Want this formula in the summary sheet. tried several ways but didn't get the desired results. I tried

=COUNTIFS([Activity Type]:[Activity Type], "Improvement", Month:Month, 1)

=COUNTIFS([Activity Type]:[Activity Type], "Improvement", [Month]:[Month], 1)

Showing result as "Invalid data type"

Please share your thoughts to fix this. Really appreciated.

Comments

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hi Rajneesh

    Is your Month column created using the Month function against a date column? I ask as I could re-create that error when errors occurred in the Month function due to blanks in the referenced date column. If yes, I fixed my error by adding an IF statement to the Month function looking first for ISDATE(whatever date column@row), MONTH(whatever date column@row))

    Do you have the same occurring in your sheet?

  • I am experiencing the same problem as Rajneesh, but with 3 columns. I am trying to count the number of rows that all meet 3 different criteria across 3 different columns. I am also getting the #INVALID DATA TYPE error.

    Does anyone know if it could possibly be due to the fact that my first range is a Checkbox column and the other two ranges are Text columns? Do they all have to be the same type of column in order for the COUNTIFS to work? I can't think of why else this formula isn't working...

    =COUNTIFS([Task]:[Task], 1, [Deadline Year]:[Deadline Year], "2021", [Deadline Month]:[Deadline Month], "1")

    @KDM I am pretty new to Smartsheet, so I'm not sure if I'm understanding your fix correctly. My Deadline Month column uses the Month function and is looking at a different date column:

    =MONTH([End Date]@row)

    How would I make this into the IF statement that you describe above?

    Thank you so much for any help you can provide! 🙂

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    @Jennifer Mayne

    A frequent problem with functions referencing dates is the functions throw errors if the date range has blanks or other non-date values. I was offering a solution to avoid an error with the Month function (and in your case also the YEAR function by first checking if the referenced date is actually a date. A more straightforward solution is using the IFERROR function along with your Month or Year function

    Without seeing your sheet I'm not certain this is the cause of errors but try this

    =IFERROR(MONTH([your date column]@row), 0)

    This will return the Month (or Year if used in conjunction with that function) if the date is valid. If an error is returned it puts a zero in the cell.

    Did that fix your error?

    Kelly

  • @KDM

    It worked!! I applied the IFERROR function to my Year, Month, and Week reference formulas and it fixed the #INVALID DATA TYPE error in my other formula!! Thank you soooooo much for your help!! 😀

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Glad that was the problem.

    cheers,

    Kelly

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!