Multiple Criteria and Date Range Formula

Options

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

Tags:

Best Answer

Answers

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Options

    @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
    Klaudia ✭✭
    Options

    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
    Leibel S ✭✭✭✭✭✭
    Answer ✓
    Options

    @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
    Klaudia ✭✭
    Options

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


    Thank you,

    Klaudia

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!