COUNTIFS per calendar month

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

• Options

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.

• ✭✭✭✭✭✭
Options

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!