Combination of CountIFS and OR

Sylvia Kay

I am trying to accomplish this:

Count If:

If {Current Development - Initiative}, ="Concur HS4"

And, If {Current Development - Hierarchy}, ="EPIC"

And If either one of these columns is not Empty

{Current Development - Q2'23} <> "", {Current Development - Q3'23} <> ""

This is the formula I used, but it shows #Invalid Operation

=COUNTIFS({Current Development - Initiative}, ="Concur HS4", {Current Development - Hierarchy}, ="EPIC", OR({Current Development - Q2'23} <> "", {Current Development - Q3'23} <> ""))

Any advice is much appreciated :-)

Find more posts tagged with

Formulas

Accepted answers

All comments

Jeff Reisman

@Sylvia Kay The #INVALID OPERATION error points to an operator issue in your formula. In this case, you're missing commas before the "not equal" signs in your OR:

Also, the equal signs in front of your text criteria are not needed. This is because the = is implied by listing the criteria for the range in the criteria range, criteria format required by COUNTIFS.

Sylvia Kay

Hi Jeff, thank you for looking at this!

I made the changes as suggested, but now I get another error: "INVALID DATA TYPE"

This is the current formula:

=COUNTIFS({Current Development - Initiative}, "Concur HS4", {Current Development - Hierarchy}, "EPIC", OR({Current Development - Q2'23}, <>"", {Current Development - Q3'23}, <>""))

Jeff Reisman

https://community.smartsheet.com/discussion/comment/366934#Comment_366934

The cause of that error is: The formula contains or references an incompatible data type, such as =INT("Hello")

In this case, the way we're using OR won't work with COUNTIFS, and that's causing the error.

This is one of those cases where using a helper column will make it work much easier. Try this: Create a helper text/number column in your source sheet called something like "DevelopmentCount" and use this formula:

=IF([Current Development - Q2'23]@row <>"", 1, 0) + IF([Current Development - Q3'23]@row <>"", 1, 0)

Make it a column formula so it applies to all rows. The logic here is that if neither cell has a value in it, the cell will equal 0; if one is not blank, the cell will equal 1, and if both are not blank, the cell will equal 2.

Now on your other sheet:

=COUNTIFS({Current Development - Initiative}, "Concur HS4", {Current Development - Hierarchy}, "EPIC", {Development Count Column Range}, >= 1)

Sylvia Kay

Hi Jeff, thank you so much, this works! Much appreciated!

Sign in to join the conversation!

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.