CountIf Day is between Start and Due Date

I'm stumped in this formula...

I wish to perform a countif when a date is between two dates.

I have a Start Date and a Due Date Column and in another sheet I have a list of days.

and I want to count how many times the day June 07th is between Start Date and Due Date

In the case below June 07th should be counted as 1, June 09th should be counted as 2


I've tried with countifs, index, collect, etc, but I'm not getting there :(

Thank you!

Tags:

Best Answer

  • Mark Safran
    Mark Safran ✭✭✭✭✭
    Answer ✓

    Hi @Ana Guilherme

    A COUNTIFS( ) should work if you make [Start Date] your first range, [Due Date] your second range, and just use greater than & less than comparisons against your query date as the criterion for both.


    Try something like this (where [Query Date] is the name of the column where your 6/7 through 6/20 values are living):

    =COUNTIFS([Start Date]:[Start Date], <=[Query Date]@row, [Due Date]:[Due Date], >=[Query Date]@row)



    -MCS

Answers

  • Mark Safran
    Mark Safran ✭✭✭✭✭
    Answer ✓

    Hi @Ana Guilherme

    A COUNTIFS( ) should work if you make [Start Date] your first range, [Due Date] your second range, and just use greater than & less than comparisons against your query date as the criterion for both.


    Try something like this (where [Query Date] is the name of the column where your 6/7 through 6/20 values are living):

    =COUNTIFS([Start Date]:[Start Date], <=[Query Date]@row, [Due Date]:[Due Date], >=[Query Date]@row)



    -MCS

  • @Mark Safran Thank you!

    Worked perfectly :)

  • Owen TP
    Owen TP ✭✭

    I'm trying to use the formula above and simply do not understand the Query date. Is this a helper column added or referencing the existing dates?

  • Ana Guilherme
    Ana Guilherme ✭✭
    edited 06/06/23

    Hello @Owen TP,

    No.

    Query Date should be substitute by the date you wish to count.

    following the example I gave (in my first post):

    Query Date can be June 07th.

    Example:

    =COUNTIFS({Start Date}, <=(June 07th), {Due Date}, >=(June 07th))

    Replace June 07th by the cell date that you wish to count

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!