How can you count cells between a given date range?

Options

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

Best Answers

  • Brent Wilson
    Brent Wilson ✭✭✭✭✭
    Answer ✓
    Options

    Have a look at the COUNTIFS Functions

    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..

    Brent C. Wilson, P.Eng, PMP, Prince2

    Facilityy Professional Services Inc.

    http://www.facilityy.com

  • Rob Trippett
    Rob Trippett ✭✭
    Answer ✓
    Options

    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


Answers

  • Brent Wilson
    Brent Wilson ✭✭✭✭✭
    Answer ✓
    Options

    Have a look at the COUNTIFS Functions

    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..

    Brent C. Wilson, P.Eng, PMP, Prince2

    Facilityy Professional Services Inc.

    http://www.facilityy.com

  • Rob Trippett
    Rob Trippett ✭✭
    Answer ✓
    Options

    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
    KaseyK ✭✭
    edited 03/08/23
    Options

    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.
    Genevieve P. Employee Admin
    Options

    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
    Brent Wilson ✭✭✭✭✭
    Options

    @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)

    Brent C. Wilson, P.Eng, PMP, Prince2

    Facilityy Professional Services Inc.

    http://www.facilityy.com

  • KaseyK
    KaseyK ✭✭
    Options

    Thank you, thats works perfectly!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!