COUNTIFS per calendar month

System

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

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