Add condition to COUNTIFS

Options
This discussion was created from comments split from: Complex Countifs Formula.

Answers

  • kshah
    kshah ✭✭
    Options

    Hi all,

    I have successfully created a countif formula that counts if a date appears in a column (RYZ101-301 enrollment Range X) that is the same date as in the Start row.

    =COUNTIF({RYZ101-301 Enrollment Range 5}, Start@row) + COUNTIF({RYZ101-301 Enrollment Range 2}, Start@row)

    Now I want to add an additional condition. Only count the value in RYZ101-301 Enrollment Range 2 and 5, if the cell in RYZ101-301 Enrollment Range 2 or 5 also has "On Treatment RYZ101" in a specified column "RYZ101-301 Enrollment Range 6" for the same row that has the date in Enrollment Range 2 or 5.


    Is this possible @Paul Newcome , you seem to be a wiz at Countifs?


    RYZ101 Enrollment range 6


    RYZ101 Enrollment Range 2 or 5:


    @Start:


    My current formula without the condition for Enrollment range 6 comes up with "2" for May 22, 2023. My goal is to eliminate the 2nd May 22 since it is "On Treatment SOC".

    Thanks,

    Kathryn

  • Hollie Green
    Hollie Green ✭✭✭✭✭✭
    Options

    You would just change the formula to a Countifs and add your range,criteria at the end

    =COUNTIFS({RYZ101-301 Enrollment Range 5}, Start@row,{RYZ101-301 Enrollment Range 6},"On Treatment RYZ101") + COUNTIFS({RYZ101-301 Enrollment Range 2}, Start@row,{RYZ101-301 Enrollment Range 6},"On Treatment RYZ101")

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!