What is wrong with my countifs data is between 2 dates formula?

toni.toni601

Hello team,

I'm trying to calculate my data if the status' end date is between 2 dates.

The formula I'm using is:

=COUNTIFS({Project Plan "Status"}, Item@row, {Project Plan "End Date"}, >[Date1]10, {Project Plan "End Date"}, <=[Date2]10, {Project Plan "Play Group"}, Item10)

In my external smartsheet project plan, I have the following columns:

Status = Disc, Des, P/S, Done

End Date = There is an end date for each status listed above

Play Group = the grouping in which each data point should be deployed

In this sheet, I'm looking to populate the 1/31/2023 column using the formula listed above. My column names for the dates are: Date1, Date2, Date3, Date4...etc...

The results I am receiving in all of the cells is 0.

My columns are set as date columns however I changed to "text/number" columns and still no values just 0.

any ideas?

Samuel Mueller

@toni.toni601 no it would have to be in a date column in order for it to recognize it as a date. both the range column "{Project Plan "End Date"}" would have to be a date column and the [Date1], [Date2] etc. would have to be date columns.

Samuel Mueller

@toni.toni601 try adding @cell

=COUNTIFS({Project Plan "Status"}, Item@row, {Project Plan "End Date"}, @cell >[Date1]10, {Project Plan "End Date"}, @cell <=[Date2]10, {Project Plan "Play Group"}, Item10)

toni.toni601

:-( Still getting results 0 (zero) when there should be data...

If my column is set as a Text/Number column, does Smartsheet still recognize the data in the cell as a date?

I tried using just the countifs between the 2 dates and still resulted in "0" zero.

Samuel Mueller

@toni.toni601 no it would have to be in a date column in order for it to recognize it as a date. both the range column "{Project Plan "End Date"}" would have to be a date column and the [Date1], [Date2] etc. would have to be date columns.

toni.toni601

Changing the column to a date column worked perfectly when I added the @cell. THANK YOU