I am trying to work with a countifs formula. I have a column titled "Event Date" Format MM/DD/YY and a column titled "Risk Level". Values in risk level are "low", "medium", "high".

I am trying to count the number of each risk level that occur each month/year.

I can get the count for each risk level ok, but adding the date has given me all sorts of errors.

Best Answers

  • David Tutwiler
    David Tutwiler Overachievers Alumni
    Answer ✓

    I think to start you're looking for:

    =COUNTIFS([Risk Level]:[Risk Level], "low", [Event Date]:[Event Date], MONTH(@cell)=1)

    This should get you all of the low level risks for January. If you're looking to add a year you can add:

    ,[Event Date]:[Event Date], YEAR(@cell)=2020)

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    Answer ✓

    If you want to count year and month you can also use the actual date format.

    =counitfs([Risk Level]:[Risk Level], "High", [Event Date]:[Event Date], AND(@cell>=Date(2020, 12 1),@cell<=DATE(2020, 12, 31)))

  • Sameer Karkhanis
    Sameer Karkhanis ✭✭✭✭✭✭
    Answer ✓

    Alternatively, you can also use the new capabilities in reports -- Grouping (by month and risk -- you may have to add a column to your sheet for month based on event date) and Summarize (aka count of risk level)


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!