# Countif with OR statement

Options
✭✭

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,

• ✭✭
Options

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,

• ✭✭✭✭✭✭
Options

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(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(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.

• ✭✭
edited 01/16/24
Options

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,

• ✭✭✭✭✭✭
Options

Are the helper columns showing the expected results?

• ✭✭
Options

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

• ✭✭
Options

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!