COUNTIF and CONTAIN Formula Help

anna2121

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

Paul Newcome

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

Paul Newcome

You have an extra closing parenthesis that needs removed after the CONTAINS function. You will need to use an "@cell" reference inside of the CONTAINS function as well.

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

anna2121

Thank you Paul - for the @cell component, that would need to be another range wouldn't it? It would be searching for "A" within a range. Please correct me if I'm misunderstanding! The reference sheet has a column of platform options so I would want it to detect Platform A within that range.

=COUNTIFS({Risk & Issue Log Range 1}, CONTAINS(Platform@row, {02 Firefly Risk & Issue Log Range 1}), {02 Firefly Risk & Issue Log Range 2}, <>"Closed", {02 Firefly Risk & Issue Log Range 3}, "Risk")) Still results in an unparseable error- appreciate your help.

anna2121

RAID Count.PNG

Paul Newcome

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

anna2121

Ah okay, thank you again Paul!!

Paul Newcome

Happy to help. 👍️