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.

Find more posts tagged with

Accepted answers

All comments

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!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.