CountIfs

s.oest

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.

David Tutwiler

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

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

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)

David Tutwiler

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

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

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)

s.oest

Thank you all! Each of these worked in their own way!

Mike Wilday

Great, glad we could help you out. Please accept our answers! :D Happy Smartsheeting!