count number of entries in a date range

Alywilk

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

Paul Newcome

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?

Paul Newcome

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)

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

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

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

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

Happy to help. 👍️

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

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

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)