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)

  • Paul Newcome
    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)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!