COUNTIF and CONTAIN Formula Help

Hello, I'm creating a view for the dashboard that counts the number of open risks, issues, and decisions from a project dealing with multiple platforms (A, B, C, D). I would love some assistance with correcting my formula. I'm referencing another sheet (Risk & Issue Log) that has the following range columns: Platform (A, B, C, D), Status (Open, In Progress, Closed), and Type (Risk, Issue). I need the formula to COUNTIFS the Platform matches, 'Open' status, and a 'Risk'.

The platforms column in the reference sheet is multi-select which is why I need to incorporate 'CONTAINS' in the COUNTIFS in the beginning. I'm getting an error and may be missing a } or )?

=COUNTIFS({Risk & Issue Log Range 1}, CONTAINS("A", {Risk & Issue Log Range 1})), {Risk & Issue Log Range 2}, <>"Closed", {02 Firefly Risk & Issue Log Range 3}, "Risk"))

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    You would leave the "@cell" portion exactly as is. I also just noticed that there is one too many closing parenthesis at the end of the formula.

    Assuming you have set up your cross sheet references appropriately, try this exactly as is:

    =COUNTIFS({Risk & Issue Log Range 1}, CONTAINS("A", @cell), {Risk & Issue Log Range 2}, <>"Closed", {02 Firefly Risk & Issue Log Range 3}, "Risk")


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!