Hi, I am trying to calculate the number of incidents that have occurred in a month.

Options

Using the following formula getting unparseable

=COUNTIF([Date:Date], MONTH(@cell)=11)

• ✭✭✭✭✭✭
Options

You're close...the range reference should be [Date]:[Date] OR Date:Date

=COUNTIF(Date:Date, MONTH(@cell)=11)

However, I think you'll still run into an INVALID DATE TYPE error.

You could also try this for a specific month and year.

=COUNTIF(Date:Date, AND(@cell >= DATE(2021, 11, 1), @cell <= DATE(2021, 11, 30))

• ✭✭✭✭✭✭
Options

You're close...the range reference should be [Date]:[Date] OR Date:Date

=COUNTIF(Date:Date, MONTH(@cell)=11)

However, I think you'll still run into an INVALID DATE TYPE error.

You could also try this for a specific month and year.

=COUNTIF(Date:Date, AND(@cell >= DATE(2021, 11, 1), @cell <= DATE(2021, 11, 30))

• Options

Thank you Jason. =COUNTIF(Date:Date, AND(@cell >= DATE(2021, 11, 1), @cell <= DATE(2021, 11, 30)) this works. If i want to calculate different months ??

• ✭✭✭✭✭✭
Options

If you have separate columns for each month, then you can just copy this formula and change the DATE values to say DATE(2021, 12, 1) and DATE(2021,12,31). Since it's using greater or equal AND less than or equal, it will work with any range of dates.

With your formula having only MONTH(@cell) = 11, then it would look for November of any year, unless you use COUNTIFS and add a second criteria for YEAR(@cell).

• Options

That is really great; thank you Jason

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!