COUNTIFS FORMULA with DATE RANGES

marf14

I have a sheet with 2,000 events with lots of details, but I am trying to generate a quick report that shows events scheduled for every month.

Table:

Event Scheduled Date Rescheduled Date

1 6/1/2024 6/10/2024

2 6/5/2024

3 6/21/2024 6/25/2024

4 6/29/2024 7/10/2024

Events 1 and 3 were rescheduled, event 2 kept it's original date. I need to count all events in June, in this case 3 (only the new date for events 1, 3). Event 4 was moved to July so I don't need to count it until next month.

I am using the following formula:

=COUNTIFS([Schedule Date]:[Schedule Date], >=DATE(2024, 6, 1), [Schedule Date]:[Schedule Date], <=DATE(2024, 6, 31) + COUNTIFS([Reschedule Date]:[Reschedule Date], >=DATE(2024, 6, 1), [Reschedule Date]:[Reschedule Date], <=DATE(2024, 6, 31)

I have added another } - COUNTIFS([Reschedule Date]:[Reschedule Date], <>"" and that is not working.

DKazatsky2

@marf14,

I don't believe I am counting twice. In my results, based on your example, I get a count of 3. If I was counting twice when the Scheduled Column is filled in and Rescheduled Column too I would get a count of 5.

The first COUNTIFS counts only if scheduled date is in June AND there is no Rescheduled Date…which is 1.

The second COUNTIFS counts any rescheduled date in June…which is 2. For a total of 3.

Courtney S.

My idea isn't really engaging with your current formula, so feel free to ignore, but counting rows would be simpler if you could look at only one column. Have you considered changing your "rescheduled date" column into an "Actual event date" column, and always entering a date into that column? Eg. row 2 would have 6/5 twice. Then you could just do a simple countifs function only with that one "Actual event date" column.

Courtney S.

To fill in the blanks quickly, if you're comfortable running an automation on the sheet (you would always make a backup first), you could use a one-time automation workflow that copies the value from the scheduled date column only if the rescheduled date column is blank.

DKazatsky2

Hi @marf14,

Give this a try.

=COUNTIFS([Scheduled Date]:[Scheduled Date], ISDATE(@cell), [Scheduled Date]:[Scheduled Date], MONTH(@cell) = 6, [Rescheduled Date]:[Rescheduled Date], ISBLANK(@cell)) + COUNTIFS([Rescheduled Date]:[Rescheduled Date], ISDATE(@cell), [Rescheduled Date]:[Rescheduled Date], MONTH(@cell) = 6)

Hope this helps,

Dave

marf14

https://community.smartsheet.com/discussion/comment/440609#Comment_440609

That would be my approach, but I need to keep the original date in the Scheduled column for clerical reasons

marf14

https://community.smartsheet.com/discussion/comment/440611#Comment_440611

I see where you are going, but you are still counting twice when the Scheduled Column is filled in and Rescheduled Column too. I only need to count the Rescheduled date once

DKazatsky2

@marf14,

I don't believe I am counting twice. In my results, based on your example, I get a count of 3. If I was counting twice when the Scheduled Column is filled in and Rescheduled Column too I would get a count of 5.

The first COUNTIFS counts only if scheduled date is in June AND there is no Rescheduled Date…which is 1.

The second COUNTIFS counts any rescheduled date in June…which is 2. For a total of 3.

marf14

https://community.smartsheet.com/discussion/comment/440616#Comment_440616

I apologize, it works beautifully. I must have had an extra bracket or something or used the wrong column, not sure. I had to type it manually because the column names are slightly different and probably had a typo

THANK YOU!!!!!!!!!!!!!!!!!!