COUNTIFS Formula based on Dates
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.
Answers
-
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
-
@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!
Help Article Resources
Categories
Check out the Formula Handbook template!