How to count the number of events in a given month regardless of year.

Omars · 07/12/22

I'm drawing data into a calc sheet from another sheet and I want to determine the number of events we've had in February (for example) over the course of several years. So I'm trying to do a countif formula for the dates but can't seem to find a workable equation. TIA for your help.

Toufong Vang · 07/13/22

Assuming that the event date is in the column named, Date_Event, your count of events for the month of February would be...

= COUNTIF( Date_Event:Date_Event, MONTH(@cell) = "2")

For March, it would be...

= COUNTIF( Date_Event:Date_Event, MONTH(@cell) = "3")

..and so on.

Hawk23 · 03/18/24

Hi @Toufong Vang ,

I tried this formula but am having difficulty. On my reference sheet, the column name is "Last Day of Employment" and the formatting of the data entered is in Short Date (e.g., 1/01/24, or 3/04/24). On my Metrics Sheet where I am trying to calculate, I am looking to sum the total number of employee resignations per month. I tried the formula but am stuck. Can you provide more guidance for me?

Toufong Vang · 03/18/24

Hi, @Hawk23, use COUNTIFS() because you're evaluating more than one range--{Last Date of Employment} and {Term Type}.

Assuming that the range [Last Date of Employment]:[Last Date of Employment] is {Last Date of Employment} (and the same for "Term Type"), the formula would be...

= COUNTIFS({Last Date of Employment}, MONTH(@cell) = 1, {Term Type}, "Resignation")

Hope this helps!

You'll additional documentation on Smartsheet functions at:

https://help.smartsheet.com/functions

Hawk23 · 03/18/24

Hi @Toufong Vang thank you, I got a little further (it no longer says unparseable, lol) but it gave me a value of 0 back for all months.

The formula I used: =COUNTIFS({Last Day of Employment}, MONTH({Last Day of Employment}) = 1, {Term Type}, "Resignation")

There should be '8' resignations displaying for January

Hawk23 · 03/18/24

@Toufong Vang

I think I found a solution, I added a hidden column into my reference sheet to calc the month based on date, and then used that as the reference in my metrics sheet and it seems to be working.:)

Toufong Vang · 03/18/24

https://community.smartsheet.com/discussion/comment/425867#Comment_425867

@Hawk23 , switch the ranges around. Count "Term Type" instead of "Last Date of Employment".

=COUNTIFS({Term Type}, "Resignation", {Last Date of Employment}, MONTH(@cell) = 1)

How to count the number of events in a given month regardless of year.

Answers

Help Article Resources

Categories