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,

Find more posts tagged with

Accepted answers

VenuG

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,

All comments

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

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

Are the helper columns showing the expected results?

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

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,

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.