Countifs with multiple criteria in the same cell

How do I countifs, if the Risk is open, status is yellow and affects group C, D, E and F. I have the below formula but do not know how to add groups D, E and F

=COUNTIFS({FPR Risks Range 1}, "Open", {FPR Risks Range 2}, "YELLOW", {Group}, CONTAINS("D", @cell))...


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Are you looking for something that has "A", "B", AND "C", or is it more like "A", "B", OR "C"?

  • Something that has "A", "B", and "C".

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    In that case you have two options. You can continue with the same syntax of range, criteria, range, criteria and just keep entering the same range with the updated criteria.

    =COUNTIFS({FPR Risks Range 1}, "Open", {FPR Risks Range 2}, "YELLOW", {Group}, CONTAINS("D", @cell), {Group}, CONTAINS("A", @cell))

    Or you can enter the range once and use an AND function to have multiple criteria sets for the same range.

    =COUNTIFS({FPR Risks Range 1}, "Open", {FPR Risks Range 2}, "YELLOW", {Group}, AND(CONTAINS("D", @cell), CONTAINS("A", @cell)))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!