COUNTIFS FORMULA with DATE RANGES

Options

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.

Best Answer

  • DKazatsky2
    DKazatsky2 ✭✭✭✭✭✭
    Answer ✓
    Options

    @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.

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!