How can you count cells between a given date range?

samstew_atlas

For example - count the number of project completed or cancelled (status) during the year 2021.

Brent Wilson

Have a look at the COUNTIFS Functions

https://help.smartsheet.com/function/countifs

This would have to be put in a sheet summary formula to work properly

Also, a report could do this quite easily with the filters and sum formulas.. You could even then group it by Month or Project Manger, etc..

Rob Trippett

You can use the CountIfS() function.

#Projects Complete:

=countifs((project complete), (project complete)@row, True, (completed date)@row, >= 1/1/2021, (completed date)@row, <= 12/31/2021)

So countfis((project complete) *looks at the project complete column and will count the field if, (project complete), True, *If the project complete field is set to True and the completed date is greater than or equal to the first day of the year, (completed date), >= 1/1/2021, and the completed date is less than or equal to the last day of the year. (completed date), <= 12/31/2021

#Projects Incomplete:

=countifs((project complete), (project complete)@row, False, (start date)@row, >= 1/1/2021, (start date)@row, <= 12/31/2021)

So countfis((project complete) *looks at the project complete column and will count the field if, (project complete), False, *If the project complete field is set to False and the start date is greater or equal to the first day of the year, (start date), >= 1/1/2021, and the start date is less or equal to the last day of the year. (start date), <= 12/31/2021

This assumes that you have a column named "Project Complete" that uses a check box (box checked = True and unchecked = False). You could also use the word "Complete" and "Incomplete" in the column, but you would then have to replace the True and False in the formula to Complete and Incomplete.

I hope this helps.

Rob

Brent Wilson

Have a look at the COUNTIFS Functions

https://help.smartsheet.com/function/countifs

This would have to be put in a sheet summary formula to work properly

Also, a report could do this quite easily with the filters and sum formulas.. You could even then group it by Month or Project Manger, etc..

Rob Trippett

You can use the CountIfS() function.

#Projects Complete:

=countifs((project complete), (project complete)@row, True, (completed date)@row, >= 1/1/2021, (completed date)@row, <= 12/31/2021)

So countfis((project complete) *looks at the project complete column and will count the field if, (project complete), True, *If the project complete field is set to True and the completed date is greater than or equal to the first day of the year, (completed date), >= 1/1/2021, and the completed date is less than or equal to the last day of the year. (completed date), <= 12/31/2021

#Projects Incomplete:

=countifs((project complete), (project complete)@row, False, (start date)@row, >= 1/1/2021, (start date)@row, <= 12/31/2021)

So countfis((project complete) *looks at the project complete column and will count the field if, (project complete), False, *If the project complete field is set to False and the start date is greater or equal to the first day of the year, (start date), >= 1/1/2021, and the start date is less or equal to the last day of the year. (start date), <= 12/31/2021

This assumes that you have a column named "Project Complete" that uses a check box (box checked = True and unchecked = False). You could also use the word "Complete" and "Incomplete" in the column, but you would then have to replace the True and False in the formula to Complete and Incomplete.

I hope this helps.

Rob

KaseyK

Im trying to use a similar formula but keep getting INVALID OPERATION:

I already have a metrics sheet tracking this info from various sheets to display on a Dashboard - now I need to segregate these metrics by year:

=COUNTIFS({Booking Request Type}, [Primary Column]@row, {cosine Booking Calendar Due Date}, >=1 / 1 / 2022, {cosine Booking Calendar Due Date}, <=12 / 31 / 2022)

Primary ROW being the three Project Status's available on the sheets:

Genevieve P.

Hi @KaseyK

If you're looking for a specific date, you'll need to wrap those numbers within a DATE Function:

DATE(yyyy, mm, dd)

or in your case, something like:

=COUNTIFS({Booking Request Type}, [Primary Column]@row, {cosine Booking Calendar Due Date}, >= DATE(2022, 01, 01), {cosine Booking Calendar Due Date}, <= DATE(2022, 12, 31))

Cheers,

Genevieve

Brent Wilson

@KaseyK since you are only looking for a year it can be very simple but as @Genevieve P. stated it needs to be in a format Smartsheet can recognize.

I haven't tested this but all you need to do is check the year

=COUNIF({Booking Request Type}, [Primary Column]@row, YEAR({cosine Booking Calendar Due Date}), = 2022)

KaseyK

Thank you, thats works perfectly!