Nested AND/OR statement with COUNTIF

Hello,

I'm trying to count the number of rows that fit this criteria:

This is the formula I came up with but am getting an error. I'm betting that the problem is the placement of the ANDs?

=COUNTIF(AND((OR({Retail Survey Launch Status} = "Operational", {Data Upload Status} = "Operational",(AND({Experiencia Rounding Status}@row<>"Operational", {Experiencia Rounding Status}@row<>"Needs Post Launch Review", {Experiencia Rounding Status}@row<>"Post Launch Review Scheduled", {Experiencia Rounding Status}@row<>"Adding a Service", {Experiencia Rounding Status}@row<>"Requested Cancellation), {Monthly DataCor Revenue}, ""."")

Thanks for your help!

Find more posts tagged with

Formulas

Accepted answers

All comments

Paul Newcome

Is the first argument supposed to be AND or OR?

deb_63_hydracor

It could be either one or both (but doesn't have to be both).

Thanks!

Paul Newcome

Because it is two different ranges, my suggestion would be to insert a hidden helper column on the source sheet and use a basic IF/OR formula to check a box on each row if either of them are checked and then reference this helper column in the COUNTIFS.

Since it is two different ranges and it is possible for either one or both to be checked, creating a formula without the helper column (while possible) is definitely more complex to avoid double counting rows.

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.