COUNTIFS Formula based on Dates

Kevin Rice

I'm trying to use the COUNTIFS Formula to count a range that has to be greater than 0 as well as within a certain month. I've gotten a formula to work but it would be cumbersome to implement in any way. Has anyone done this with success??

It would look something like this:

=(COUNTIFS({Kettle 1}, AND({Month}, DATE(2020, 10, 1), <= DATE(2020, 10, 31))) / 12

It has to be divided by 12 because there are readings taken 12 times daily at 2-hour intervals.

"Date" column would be your "Month" in the formula.

"Kettle 1 - Firebox" column would be your "Kettle 1" in the formula.

John Jonassen

Hi @Kevin Rice ,

Looks like you might be missing a criterion. What Kettle are you wanting to count in your formula? Have a look at this and see if this works, using "1863" as the Kettle 1 example.

=COUNTIFS([Kettle 1 - Firebox]:[Kettle 1 - Firebox], "1863", Date:Date, >=DATE(2020, 10, 1), Date:Date, <=DATE(2020, 10, 31)) / 12

Kevin Rice

@John Jonassen That worked like a charm with the exception these were cross-sheet references so I had to go out and click the columns I wanted them to look at.

Thanks for the help!