How do I count the number of times dates appear within a range?

Hi,

I am trying to track the number of times a call occurs within a date range in a cross sheet reference formula. The user types the date on the call log and I would like to use the dates as a way to measure how many times calls occur within a week, month, and project to date

.

I have tried different ways, but I am stuck on such a simple thing!

=COUNT({WOB's General Call Log Range 1}(AND(@cell >= CALL DATE(2020, 04, 26), @cell <= DATE(2020, 05, 2))))

I also used COUNTIF and COUNTIFS and still received an error message.


Thank you so much,

Tara

Answers

  • Jon Baier
    Jon Baier ✭✭✭✭

    Try this

    =COUNTIFS({WOB's General Call Log Range 1}, <=(DATE(2020, 5, 2)), {WOB's General Call Log Range 1}, >=(DATE(2020, 4, 26)))

  • Tara M
    Tara M ✭✭

    Thank you so much for commenting, for some reason this didn't work either.

  • Has anyone ever solved this?

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @benhamilton1

    Can you post screen captures of your current set-up, along with a description of what you're looking to achieve and the formula you've tried? (But please block out sensitive data)

    The structure above should work as long as the column is a Date type of column:

    =COUNTIFS({Date Column}, <= DATE(yyyy, mm, dd), {Date Column}, >=DATE(yyyy, mm, dd))

    Cheers,

    Genevieve

  • Genevieve,

    The date is a precise date & time creating an unparseable error regardless of the formula.


  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @benhamilton1

    Can you post the formula you're using?

    The Created column is a System column so it's a bit different than a regular date column, but the formula should still work.

    Keep in mind that the first date you identify is the highest date, since you're looking for less than that date. Then the second one is the earliest date, as you're looking for greater than that date. Does that make sense?

    =COUNTIFS({Date Column}, <= DATE(yyyy, mm, dd), {Date Column}, >=DATE(yyyy, mm, dd))

    Cheers,

    Genevieve

  • benhamilton1
    benhamilton1 ✭✭✭
    edited 04/04/23

    =COUNTIFS({Other Sheet Range 1}, ">=" & DATE(2023,3,1), {Other Sheet Range 1}, "<=" & DATE(2023,3,31), {Other Sheet Range 2}, "<>")

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @benhamilton1

    Thanks for posting, this helps a lot!

    1) We'll want to jump right into the >= operators without any quotes. The quotes turn it into text to search for, instead of instructions in the formula.

    2) We'll want to remove the & symbol and go right into the DATE() instruction.

    3) Can you clarify what you want in the last range? Is "<>" what you're searching for, or are you saying "not equals"... something?


    Try:

    =COUNTIFS({Other Sheet Range 1}, >=DATE(2023,3,1), {Other Sheet Range 1}, <=DATE(2023,3,31), {Other Sheet Range 2}, "<>")

    or

    =COUNTIFS({Other Sheet Range 1}, >=DATE(2023,3,1), {Other Sheet Range 1}, <=DATE(2023,3,31), {Other Sheet Range 2}, <> "")

    If you meant to search for "not blank".

    Cheers,

    Genevieve

  • That one works, thank you!

  • Genevieve P.
    Genevieve P. Employee Admin

    Awesome, I'm glad I could help!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!