count number of entries in a date range
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
-
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?
-
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)
Answers
-
You should be able to reference the Created column directly.
What is the formula you are using in the Date Requested column?
-
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
-
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?
-
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!
-
Happy to help. 👍️
The @cell reference tells the formula to evaluate the previously mentioned range on a cell by cell basis.
-
@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))
-
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
Categories
Check out the Formula Handbook template!
