Welcome!

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.

Find more posts tagged with

Comments

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

Sign in to join the conversation!

Quick Links

Trending discussions

How to create automation to move a row to another sheet once CREATED DATE reached 5 days?
How do I set up the automation to move a row once the created date reaches 5 days?
Collect Data Based on Today's Date
Hi all. I'm trying to do something that I'm not sure if it's possible or not. We have a sheet that we are importing where the first column lists areas within our factory. Each column after that has a column name of a date. So 12/15/2025, 12/16/2025, 12/17/2025, etc. In each of the date columns is a target production…
SUMIFS using Dropdown
Hello! I have what is probably a simple question my brain is just not comprehending properly. I need to SUMIFS from a sheet that matches some specific criteria but one of the fields I am using against needs to calculate a total if one dropdown field matches ANY ONE of these 3 items that is in it to be chosen… Capitol…