How can I create a COUNTSIF within a specified time period?

Options

the formula I currently have is:

=COUNTIFS([Project Status]:[Project Status], CONTAINS("Started", @cell), [Area/Region]:[Area/Region], ="Canada Johnston")

Now I need to have it pull information for the contract year so between say April 1, 2023 to March 31, 2024. I can not seem to be able to add the correct information.


Thanks

Best Answers

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer βœ“
    Options

    Hi @ShawnaK,

    You would do something like this (using a column header of "Contract Date" in this example):

    =COUNTIFS([Project Status]:[Project Status], CONTAINS("Started", @cell), [Area/Region]:[Area/Region], ="Canada Johnston",[Contract Date]:[Contract Date],AND(@cell>=DATE(2023,4,1),@cell<=DATE(2024,3,31))

    If you needed to change the dates, then you would just adjust the numbers in the DATE formulas (first number is year, then month, then day).

    Hope this helps, if there are any problems/questions then just post! πŸ™‚

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer βœ“
    Options

    Yes, you can use this as range/criteria in a SUMIF(s).

Answers

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer βœ“
    Options

    Hi @ShawnaK,

    You would do something like this (using a column header of "Contract Date" in this example):

    =COUNTIFS([Project Status]:[Project Status], CONTAINS("Started", @cell), [Area/Region]:[Area/Region], ="Canada Johnston",[Contract Date]:[Contract Date],AND(@cell>=DATE(2023,4,1),@cell<=DATE(2024,3,31))

    If you needed to change the dates, then you would just adjust the numbers in the DATE formulas (first number is year, then month, then day).

    Hope this helps, if there are any problems/questions then just post! πŸ™‚

  • ShawnaK
    ShawnaK ✭✭
    Options

    Thank you so very much for your quick answer! I appreciate it.

  • ShawnaK
    ShawnaK ✭✭
    Options

    Would the same apply for SUMIFS?

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer βœ“
    Options

    Yes, you can use this as range/criteria in a SUMIF(s).

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!