Countif formula with date range

Hi,

I'm looking for a formula which will give me the total number rows for a certain date range from my master list for my metrics.


For example, I want to count the number of rows which have dates between 01/01/2024 and 01/31/24, so this will tell me how many county request have been captured within that date range.

Can someone please help?

Formula: =COUNTIFS({Ombud Complaint and Constituent Issue Trac Range 6}, [Primary Column]@row, >=DATE(2023, 12, 31), {Ombud Complaint and Constituent Issue Trac Range 3}, <=DATE(2024, 1, 31))

Answers

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    edited 01/11/24

    Hi @Canteaug,

    Your COUNTIFS is missing a column (for the first date criteria), but you can also simply this a bit further with an AND. Your formula would be something like this:

    =COUNTIFS({County reference},[Primary column]@row,{Date column},AND(@cell > DATE(2023, 12,31), @cell <= DATE(2024, 1, 31)))

    The {x} references here have been here to say which column they should correlate too, you may have them already set as the default numbered ones so when setting up your formula may look a little bit different if they're already created.

    Hopefully this helps though, but if you have any problems/questions then just post! 🙂

  • Zachary Hall
    Zachary Hall ✭✭✭✭✭✭

    You have two different ranges for the count ranges of the dates. Is that intentional? Are you looking to see if a start date is after 12/31 and the end date before 1/31? That error message generates when the size of two different ranges don't match in a function like this where they need to.

    Another note, I would remove the "=" after the ">" in your first criterion if you don't want to capture 12/31.

    I hope this helps!

    Best,

    Zach Hall

    Training Delivery Manager / Charter Communications

  • Canteaug
    Canteaug ✭✭✭

    @Zachary Hall So I'm trying to count the number of each county for every month. I am pulling the dates received and the county information from my master sheet.

  • Canteaug
    Canteaug ✭✭✭

    @Nick Korna I tried the formula you suggested and added the date column that is pull from the master and still no luck :(

  • Zachary Hall
    Zachary Hall ✭✭✭✭✭✭

    Okay. Try this:

    • =COUNTIFS({Ombud Complaint and Constituent Issue Trac Range 6}, [Primary Column]@row, {Ombud Complaint and Constituent Issue Trac Range 3}, >DATE(2023, 12, 31), {Ombud Complaint and Constituent Issue Trac Range 3}, <=DATE(2024, 1, 31))

    Best,

    Zach Hall

    Training Delivery Manager / Charter Communications

  • Canteaug
    Canteaug ✭✭✭

    @Zachary Hall You saved my life! Thank you so much. :)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!