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!

Find more posts tagged with

Accepted answers

All comments

Meghan Minehan

Update: This is what I've attempted and is not working

ron.judenberg112096

=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)

Paul Newcome

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)

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.