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.


image.png

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

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

Answers

  • John Jonassen
    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
    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!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!