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)

Best Answer

  • Jason Duryea
    Jason Duryea ✭✭✭✭✭✭
    Answer ✓
    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))

Answers

  • Jason Duryea
    Jason Duryea ✭✭✭✭✭✭
    Answer ✓
    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))

  • Sean Turner
    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 ??

  • Jason Duryea
    Jason Duryea ✭✭✭✭✭✭
    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).

  • Sean Turner
    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!