Multiple Criteria and Date Range Formula

Klaudia

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

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))

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