Countif with OR statement
Hi,
Looking for some assistance on a cross sheet formula
I made the filter using below screen criteria in the metric sheet, but unable to make it in Countif using OR into Intake Sheet to show the numbers in Widget.
I tried with below formula:
=COUNTIFS({RiskCategory}, "", {RiskDescription}, "", {RiskMitigationPlan}, "") , OR(COUNTIFS({BudgetHealth}, ="Red", {ProjectHealth}, ="Red")
Thanks,
Best Answer
-
Hi Nick,
I got the solution, after so many iterations, finally got it & Thank you for your time.
Here is the formula:
=COUNTIFS({RiskCategory}, ="", {RiskDescription}, ="", {RiskMitigationPlan}, ="", {BudgetHealth}, OR(@cell = "Red"), {ProjectHealth}, OR(@cell = "Red"))
Thanks,
Answers
-
Hi @VenuG,
You can do this with a couple of helper columns on the metric sheet.
The first one is to count the number of blanks in the 3 Risk types:
=IF(COUNTIF([Risk Category]@row, "") + COUNTIF([Risk Description]@row, "") + COUNTIF([Risk Mitigation]@row, "")>=1,1,0)
If the rows are all adjacent you can use this instead:
=IF(COUNTIF([Risk Category]@row:[Risk Mitigation]@row, "")>=1,1,0)
The second checks the 2 health columns in a similar way.
=IF(COUNTIF([Budget Health]@row, "Red") + COUNTIF([Project Health]@row, "Red")>=1,1,0)
If adjacent:
=IF(COUNTIF([Budget Health]@row:[Project Health]@row, "Red")>=1,1,0)
You can then use these in your cross sheet COUNTIFS formula:
=COUNTIFS({Helper 1},1,{Helper 2},1)
It probably is possible to write a single formula, but given the number of criteria this is probably a quicker way of doing it.
Hope this helps, but if there are any problems/questions then just let us know.
-
Hi Nick,
Thanks for your reply, but after applying the above formula it showing as 0 as final output, whereas couting the blanks in RiskCategory, RiskDescription, RiskMitigationPlan have blanks & Even for BudgetHealth, ProjectHealth also having Red the output showing as 0
But, When I'm trying to apply the filter criteria using the below sheet I'm getting the number.
So, Is there Countif formula using as replica with the filters.
Thanks,
-
Are the helper columns showing the expected results?
-
helper columns showing not match values, looking for "=COUNTIFS({RiskCategory}, ="Blank", {RiskDescription}, ="Blank", {RiskMitigationPlan}, ="Blank", OR({ProjectHealth}, ="Red"), {BudgetHealth}, ="Red")) fix this formula, but don't know where I'm missing, the formula showing as '#Unparseable' error
-
Hi Nick,
I got the solution, after so many iterations, finally got it & Thank you for your time.
Here is the formula:
=COUNTIFS({RiskCategory}, ="", {RiskDescription}, ="", {RiskMitigationPlan}, ="", {BudgetHealth}, OR(@cell = "Red"), {ProjectHealth}, OR(@cell = "Red"))
Thanks,
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!