Multiple Criteria and Date Range Formula

Hi,

I am trying to create a formula that will reference another sheet, look at two criteria within the same column and a date range between two columns.

The following works to get the two criteria from a column but I cant seem to be able to add the date range to it.

=COUNTIFS({Sheet - Status}, OR(CONTAINS("Complete", @cell), CONTAINS("Active", @cell)))

I would like to count the 'complete' and 'active' that are in the date range:

1st Date column look at dates that are: less than 1st February 2023

2nd Date column look at dates that are: greater than 31st December 2022

Thank you,

Klaudia

Find more posts tagged with

Formulas

Accepted answers

Leibel S

@Klaudia

Just take out the CONTAINS function from the formula:

=COUNTIFS({Sheet - Status}, OR(@cell="Complete", @cell="Active"), {DATE COLUMN REFERENCE},@cell<DATE(2023,2,1))

All comments

Leibel S

@Klaudia Keep on adding to your formula the date columns and criteria you want:

e.g =COUNTIFS({Sheet - Status}, OR(CONTAINS("Complete", @cell), CONTAINS("Active", @cell)), {DATE COLUMN REFERENCE},@cell<DATE(2023,2,1))

Klaudia

Thank you, this is really helpful. I am not getting the correct result as some cells in the Status column contain the words "complete" and "active" but also "complete milestone" and "active milestone". It is counting those cells as well. How do I adapt the formula to only count cells with "complete" or "active" text only?

Thank you,

Klaudia

Leibel S

@Klaudia

Just take out the CONTAINS function from the formula:

=COUNTIFS({Sheet - Status}, OR(@cell="Complete", @cell="Active"), {DATE COLUMN REFERENCE},@cell<DATE(2023,2,1))

Klaudia

@Leibel Shuchat Thank you very much for your help. This is now working the way I need it.

Thank you,

Klaudia

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.