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
-
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")
Answers
-
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"))
-
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.
-
-
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")
-
Ah okay, thank you again Paul!!
Help Article Resources
Categories
Check out the Formula Handbook template!