COUNTIFS function showing error

Rajneesh Sharma

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.

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?

Jennifer Mayne

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

@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

Jennifer Mayne

@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

Glad that was the problem.

cheers,

Kelly