Countifs using multiple columns + if date is in the last 7 days

Hi all,

I have two different formulas that I want to combine. They work separately, but not combined.

Formula one
=COUNTIFS([Roof AHU 1&2 Base Build Supply Fan]:[Roof Lights], "Yellow")

Formula two
=COUNTIF([Date of Plant Check]:[Date of Plant Check], >TODAY(-7))

I want my formula to pick up where the symbol is yellow but only if it's corresponding date is in the last 7days. I get an incorrect argument set message when I combine both

=COUNTIFS([Date of Plant Check]:[Date of Plant Check], >TODAY(-7), [Roof AHU 1&2 Base Build Supply Fan]:[Roof Lights], "Yellow")

This formula is reading 21 columns in total.

Many thanks for your help

Find more posts tagged with

Formulas

Accepted answers

Carson Penticuff

You cannot mix ranges in that manner, each range within the COUNTIFS() must be the same size. For this method, you would need to use a complicated OR() statement and list each column individually.

Are you able to add a helper column to your sheet? If so, you can use that column to determine if any cells in that row are yellow and then reference that column from your existing row.

Helper Column "Contains Yellow" (Checkbox)
=IF(COUNTIFS([Roof AHU 1&2 Base Build Supply Fan]@row:[Roof Lights]@row, "Yellow") > 0, 1, 0)

Existing Column
=COUNTIFS([Date of Plant Check]:[Date of Plant Check], >TODAY(-7), [Contains Yellow]:[Contains Yellow], @cell = 1)

You can also hide the helper column; there is no need for it to be visible.

Paul Newcome

That is because all ranges within a function must be of the same size and shape. My suggestion would be some helper columns to count each color on every row then use a SUMIFS to get the total count based on the date range.

All comments

Carson Penticuff

You cannot mix ranges in that manner, each range within the COUNTIFS() must be the same size. For this method, you would need to use a complicated OR() statement and list each column individually.

Are you able to add a helper column to your sheet? If so, you can use that column to determine if any cells in that row are yellow and then reference that column from your existing row.

Helper Column "Contains Yellow" (Checkbox)
=IF(COUNTIFS([Roof AHU 1&2 Base Build Supply Fan]@row:[Roof Lights]@row, "Yellow") > 0, 1, 0)

Existing Column
=COUNTIFS([Date of Plant Check]:[Date of Plant Check], >TODAY(-7), [Contains Yellow]:[Contains Yellow], @cell = 1)

You can also hide the helper column; there is no need for it to be visible.

Paul Newcome

Sam Swain

Thank you both, in the end I have created a separate sheet with countif formulas for each individual column counting if the row is "Yellow" and the created date is within the last two weeks. From there I will create a report that filters any rows counting above 10 - this will alert us if there are any faults not being attended to.

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.