Formula for COUNTIF using a generic date range
Hi! I'm trying to come up with two date formulas that will tell me if (1) 3 or more events occurred in any given week (Sunday - Saturday) and (2) if 5 or more events occurred in any given month. The formula needs to check three different columns - Name, Event Date, and Situation. If the same Name has had the same Situation occur 3+ times in one week, I need the flag column to turn on. This is the formula without checking the Date column that has been working so far: =IF(COUNTIFS([Individual's Name]:[Individual's Name], [Individual's Name]@row, [MUI Type]:[MUI Type], [MUI Type]@row) >= 3, 1). I can't seem to find a formula that doesn't require a definite start and/or end date - this needs to be able to check from the beginning of time to the end of time and work within the 7-day calendar week and calendar months.
Thanks!
Answers
-
Update: This is what I've attempted and is not working
-
=IF(OR(COUNTIFS(Name:Name, Name@row, Type:Type, Type@row, Dt:Dt, WEEKNUMBER(@cell) = WEEKNUMBER(Dt@row)) >= 3, COUNTIFS(Name:Name, Name@row, Type:Type, Type@row, Dt:Dt, MONTH(@cell) = MONTH(Dt@row)) >= 5), 1, 0)
-
This can be done with 1 catch... Using the WEEKNUMBER function, the week is actually Monday through Sunday. If that is acceptable, then a possible solution would look like this:
Week number:
=COUNTIFS([Individual's Name]:[Individual's Name], [Individual's Name]@row, [MUI Type]:[MUI Type], [MUI Type]@row, [Even Date]:[Event Date], AND(IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER([Event Date]@row), IFERROR(YEAR(@cell), 0) = YEAR([Even Date]@row))) >= 3
Month:
=COUNTIFS([Individual's Name]:[Individual's Name], [Individual's Name]@row, [MUI Type]:[MUI Type], [MUI Type]@row, [Even Date]:[Event Date], AND(IFERROR(MONTH(@cell), 0) = MONTH([Event Date]@row), IFERROR(YEAR(@cell), 0) = YEAR([Even Date]@row))) >= 5
Nested in an OR as the criteria in the IF to trigger the flag:
=IF(OR(COUNTIFS([Individual's Name]:[Individual's Name], [Individual's Name]@row, [MUI Type]:[MUI Type], [MUI Type]@row, [Even Date]:[Event Date], AND(IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER([Event Date]@row), IFERROR(YEAR(@cell), 0) = YEAR([Even Date]@row))) >= 3, COUNTIFS([Individual's Name]:[Individual's Name], [Individual's Name]@row, [MUI Type]:[MUI Type], [MUI Type]@row, [Even Date]:[Event Date], AND(IFERROR(MONTH(@cell), 0) = MONTH([Event Date]@row), IFERROR(YEAR(@cell), 0) = YEAR([Even Date]@row))) >= 5), 1)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 437 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 67 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!