COUNTIFS FORMULA with DATE RANGES
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
-
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
-
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.
-
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.
-
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
-
That would be my approach, but I need to keep the original date in the Scheduled column for clerical reasons
-
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
-
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.
-
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!!!!!!!!!!!!!!!!!!
Help Article Resources
Categories
Check out the Formula Handbook template!