COUNTIFS per calendar month

This discussion was created from comments split from: Count of cells in a date range.


  • John Middleton

    Looking to use a similar function by counting up the number of times an entry is made in a calendar month. However tried just using the formula above and this doesn't work for me, any reason why not, is it the UK date ranges are different?

    =COUNTIFS([Plan Date]1:[Plan Date]17, >=DATE(1, 3, 2020), [Plan Date]1:[Plan Date]17, <=DATE(31, 3, 2020))

    to me this should return the amount of rows inside of March 2020

    I do want to go one further and split this out by different plants, but thought if I get the date counting right the extra part should be simple.

    any help is appreciated.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @John Middleton

    The DATE function is:

    DATE(yyyy, mm, dd)

    You can also use

    IFERROR(MONTH(@cell), 0) = 3

    IFERROR(YEAR(@cell), 0) = 2020

    =COUNTIFS([Plan Date]1:[Plan Date]17, AND(IFERROR(MONTH(@cell), 0) = 3, IFERROR(YEAR(@cell), 0) = 2020))

    This allows you to not worry about how many days are in the month. Just update the month number and year number, and you are set.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!