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

  • VenuG
    VenuG ✭✭
    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

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭

    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.

  • VenuG
    VenuG ✭✭
    edited 01/16/24

    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,

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭

    Are the helper columns showing the expected results?

  • VenuG
    VenuG ✭✭

    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

  • VenuG
    VenuG ✭✭
    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,

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!