Issue using COUNTIF function with OR condition where OR is reference to different sheet

I am trying to use COUNTIF function with OR condition where the OR is referencing to different sheet. It’s the OR that's causing me issues. When I refer to same sheet reference its working fine but when I use different sheet reference I get “#Invalid Operation” error.

Formula that I am using is

=COUNTIFS({Primary_Owner}, PC@row, {Priority}, "Medium", {Status}, OR("WIP", "Open"))

I am trying to look for a formula, that counts number of issues that are assigned to a Primary Owner where the priority is “Medium” and Status is either WIP or Open.

Can anyone help on how to achieve this using countifs with OR condition, or is there any other formula which I can use to achieve this results?

Find more posts tagged with

Sheets

Formulas

functionality

Community Platform

Project Management

Accepted answers

Paul Newcome

You need an "@cell" reference for each of the OR arguments.

=COUNTIFS({Primary_Owner}, PC@row, {Priority}, "Medium", {Status}, OR(@cell = "WIP", @cell = "Open"))

All comments

Paul Newcome

You need an "@cell" reference for each of the OR arguments.

=COUNTIFS({Primary_Owner}, PC@row, {Priority}, "Medium", {Status}, OR(@cell = "WIP", @cell = "Open"))

Komal Lakshminarasimha

@Paul Newcome - Thanks Paul. I did try this earlier but didn't work. But now, i retried it again after your suggestion and it did work. Thanks a lot.

Paul Newcome

Happy to help. 👍️

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.