COUNTIFS, HAS, and AND?

I am trying to figure out how to complete a formula using COUNTIFS, HAS, and AND, if that is even the correct route to go!

I need to count the number of times a group of resources occur in 3 separate projects within a single program plan. I have a column for “Group” but we need to roll up a number of groups into one upper level category, that is, client wants to see the number of IT tasks which could be any task with Server, Database or Help Desk assigned. I am able to create a formula for one:

=COUNTIFS([Project Name]:[Project Name], "House", Group:Group, HAS(@cell , "Server"))

But how do I add the other two IT sub-groups to that line so that it comes up with a single total for all 3? I thought perhaps using AND??

Right now, I have it the numbers using a helper column but I don’t want to use that method going forward in case I forget to click it as resources are assigned.

Find more posts tagged with

Accepted answers

Paul Newcome

Adding the multiple COUNTIFs will give you an incorrect total as you could double and even triple count. You also do not need a helper column if you use the OR function inside of the COUNTIFS like so:

=COUNTIFS([Project Name]:[Project Name], @cell = "House", Group:Group, OR(HAS(@cell, "Server"), HAS(@cell, "Database"), HAS(@cell, "Help Desk")))

Paul Newcome

That is correct. Think of the arguments as being applied to one cell at a time. You wanted to count a cell if it was one or the other. A cell that has "this" OR "that" OR "something else".

You didn't want to limit it to cells that contained all of them at the same time. A cell that has "this" AND "that", AND "something else".

All comments

amarkumar

@Pamela Wagner Smartsheet COUNTIFS doesn’t support OR/AND logic across multiple HAS() criteria in a single criteria slot. The two clean options are:

1.) add 3 COUNTIFS
Use this if each row can only match one subgroup (no double-count risk):

=COUNTIFS([Project Name]:[Project Name], "House", Group:Group, HAS(@cell , "Server"))

COUNTIFS([Project Name]:[Project Name], "House", Group:Group, HAS(@cell , "Database"))

COUNTIFS([Project Name]:[Project Name], "House", Group:Group, HAS(@cell , "Help Desk"))

2.) If Group can contain multiple values: formula-driven helper (no clicking)

Create an [IT?] checkbox (or flag) column with a column formula:
=OR(HAS([Group]@row , "Server"), HAS([Group]@row , "Database"), HAS([Group]@row , "Help Desk"))

Then count:
=COUNTIFS([Project Name]:[Project Name], "House", [IT?]:[IT?], 1)

(If your “Group” column can contain multiple of those words on the same row, 1.) can double count; 2.) won’t.)

Pamela Wagner

Thank you @amarkumar but I was really hoping for one formula that used multiple functions as I did not want to rely on a Helper column. The Helper column, while it does work as I have created it, introduces error as now I have to remember to check that column whenever a resource is added or changed.

amarkumar

@Pamela Wagner you can try :- =COUNTIFS([Project Name]:[Project Name], "House", Group:Group, OR(HAS(@cell , "Server"), HAS(@cell , "Database"), HAS(@cell , "Help Desk")))

Paul Newcome

=COUNTIFS([Project Name]:[Project Name], @cell = "House", Group:Group, OR(HAS(@cell, "Server"), HAS(@cell, "Database"), HAS(@cell, "Help Desk")))

Pamela Wagner

@Paul Newcome YES! That worked! So it was an OR and not AND.

Paul Newcome

That is correct. Think of the arguments as being applied to one cell at a time. You wanted to count a cell if it was one or the other. A cell that has "this" OR "that" OR "something else".

You didn't want to limit it to cells that contained all of them at the same time. A cell that has "this" AND "that", AND "something else".

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.