Sheet Summary box COUNTIFS + AND/OR

I am trying to do something similar on the summary of a sheet....

I want to count if proposed start date column is less than or equal to today, as well as if the PC 2 column = one of "S1", "S2", "S3","S4","S5","S6","S7","S8","S9".

I attempted by just trying "S1","S2","S3"....

=COUNTIFS([Proposed Start Date]:[Proposed Start Date], <=TODAY(), [PC 2]:[PC 2], AND(OR(@cell= "S1", @cell= "S2", @cell= "S3")))

The above did not work so I tried the below:

=COUNTIFS([Proposed Start Date]:[Proposed Start Date], <=TODAY(), [PC 2]:[PC 2], AND(OR([PC 2]:[PC 2] = "S1", [PC 2]:[PC 2] = "S2", [PC 2]:[PC 2] = "S3")))

Any suggestions?? please

😊

Find more posts tagged with

Formulas

Sheet Summary

Accepted answers

Paul McGuinness

Hi @Stacy Meadows

You did have it, was just inclusion of the AND statement, as the COUNTIFS requires all criteria to be met to count so the AND isn't needed.

Try this

=COUNTIFS([Proposed Start Date]:[Proposed Start Date], <=TODAY(), [PC 2]:[PC 2], OR(@cell= "S1", @cell= "S2", @cell= "S3"))

Hope that helps

Thanks

Paul

All comments

Paul McGuinness

Hi @Stacy Meadows

You did have it, was just inclusion of the AND statement, as the COUNTIFS requires all criteria to be met to count so the AND isn't needed.

Try this

=COUNTIFS([Proposed Start Date]:[Proposed Start Date], <=TODAY(), [PC 2]:[PC 2], OR(@cell= "S1", @cell= "S2", @cell= "S3"))

Hope that helps

Thanks

Paul

Stacy Meadows

@Paul McGuinness Thank you so much that worked a treat 😘

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.