count number of entries in a date range

Alywilk
Alywilk ✭✭✭
edited 08/16/22 in Formulas and Functions

I'm trying to do the same thing as this (https://community.smartsheet.com/discussion/55591/countifs-between-two-dates) although I am still getting a "0" instead of an actual count. I'm trying to count the requests come in each week and my current formula is:


=COUNTIFS({MaxEnroll Support Request Tracking Range 3}, >=DATE(2022, 6, 12), {MaxEnroll Support Request Tracking Range 3}, <=DATE(2022, 6, 18))


I have changed the date range, tried swapping the <> symbols and no matter what I do I still get zero however my sheet definitely has dates in the ranges i am inputting. The column it is requesting the date from is only a date but it is in 06/05/2022 format. does that matter?

Edited to add: Some additional info that is probably relevant. the date range it is pulling from is actually a column that is calculating the date based off a formula so I wonder if that is why the formula isn't working.


These entries are coming from when people fill out a form, and so we have the "Created" column auto calculating the date it is submitted. I couldn't figure out to get the formula to work with that column so that is why I created the "date requested" column to extract just the date alone without the time stamp but I guess the formula is messing things up.

So if there is a way to make this formula to work with the "created" column that would be great I could not figure out to remove the timestamp off of the date there

Best Answers

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You should be able to reference the Created column directly.


    What is the formula you are using in the Date Requested column?

  • Alywilk
    Alywilk ✭✭✭

    The "Date Requested" column has this formula =DATEONLY(Created@row) to extract the date alone from the Created column.

    This formula below, is on a different sheet I'm using to pull together the week over week requests so it is not on the same Sheet. I have tried this same formula and changed the reference to the Created row or the Date requested row and either way I still get all 0's

    =COUNTIFS({MaxEnroll Support Request Tracking Range 3}, >=DATE(2022, 6, 12), {MaxEnroll Support Request Tracking Range 3}, <=DATE(2022, 6, 18))

    Although the snapshot above is not showing dates in June, we have a few hundred entries and know I have 5 that came in on the week of June 12-18

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Lets try adding in "@cell" references...


    =COUNTIFS({MaxEnroll Support Request Tracking Range 3}, @cell >=DATE(2022, 6, 12), {MaxEnroll Support Request Tracking Range 3}, @cell <=DATE(2022, 6, 18))


    If that doesn't work, what happens if you adjust the dates in the COUNTIFS to cover the rows shown in your last screenshot?

  • Alywilk
    Alywilk ✭✭✭

    Thanks @Paul Newcome This worked!

    I had seen formulas with the @cell before but thought that would only work if I was referencing a single cell but the fact that I needed to count from the whole column was confusing me. I double checked my counts and they are right so thank you!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️


    The @cell reference tells the formula to evaluate the previously mentioned range on a cell by cell basis.

  • Alywilk
    Alywilk ✭✭✭

    @Paul Newcome We are wanting to add another layer of complexity to the formula 😁 is it possible to add if box in another column is checked? so count entries that are between the 2 dates AND have a box checked in another column? my attempts are throwing various errors (this is a cross-sheet formula)


    This is the base formula: =COUNTIFS({MaxEnroll Support Request Tracking Range 1}, @cell >= DATE(2022, 6, 1), {MaxEnroll Support Request Tracking Range 1}, @cell <= DATE(2022, 7, 4))

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Yes. You would continue with the "range, criteria" syntax.


    =COUNTIFS({MaxEnroll Support Request Tracking Range 1}, @cell >= DATE(2022, 6, 1), {MaxEnroll Support Request Tracking Range 1}, @cell <= DATE(2022, 7, 4), {Checkbox Range}, @cell = 1)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!