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

Options
edited 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.

• ✭✭✭✭✭
Options

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.

• ✭✭
Options

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?

• ✭✭✭✭✭
Options

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:

• ✭✭
Options

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

• ✭✭
Options

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.:)

• ✭✭✭✭✭
Options

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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!