COUNTIFS returning 0

I've been using COUNTIFS within a report with 3 criteria with no issues until I tried to add a 4th criteria. I'm thinking it has to do with pulling 2 different criteria from the same column as when I tried it with a 4th criteria but from a different column it worked. When I tried pulling the 4th criteria from the same column, I also tried to give the area a different name but that also did not work (so the same column but I added a "2" ot the field Sheet Reference Name).

My goal is to determine the total number of tickets that that an Agent in the Telecom Group takes with a ticket type of "Phones" and also "IPC".

Here is what I tried but did not work:

=COUNTIFS({Group}, "Telecom”, {Agent}, "Mickey Mouse", {TixType}, "Phones", {TixType}, “IPC”)

= COUNTIFS({Group}, "Telecom", {Agent}, " Mickey Mouse ", {TixType}, "Phone", {TixType2}, “IPC”)

Worked with a different column for a 4th criteria:

= COUNTIFS({Group}, "Telecom", {Agent}, " Mickey Mouse ", {TixType}, "Phone", {Dept}, “LC”)

So the problem seems to be trying to pull 2 criteria from the same column. Is there a fix?

Find more posts tagged with

Accepted answers

Nic Larsen

Try:

=COUNTIFS({Group}, "Telecom”, {Agent}, "Mickey Mouse", {TixType}, AND(@cell = "Phones", @cell = "IPC")

Nic Larsen

You may also want to try OR instead of AND. If that doesn't work, can you post your formula or a screenshot of it hiding anything else that shouldn't be shared?

@cell function allows a formula to look within a range for the keyword in this case. So for your range TixType, it scans the column and looks for when it finds either of the two words. It's very useful.

KPH

To count things from a multi-select drop down, you will need @cell but you will also need a HAS function.

The syntax would be:

=COUNTIF({range}, HAS(@cell , "thing to look for within the multi select" ))

https://help.smartsheet.com/function/has

All comments

Nic Larsen

Try:

=COUNTIFS({Group}, "Telecom”, {Agent}, "Mickey Mouse", {TixType}, AND(@cell = "Phones", @cell = "IPC")

Pamela Wagner

Nope, still received a 0. What does the @cell refer to?

Nic Larsen

You may also want to try OR instead of AND. If that doesn't work, can you post your formula or a screenshot of it hiding anything else that shouldn't be shared?

Pamela Wagner

YES! OR worked!

Thanks much!

Pamela Wagner

On another note:

@cell function allows a formula to look within a range for the keyword in this case . So for your range TixType, it scans the column and looks for when it finds either of the two words. It's very useful.

Could this help me when I've got a column that has a drop down field when you can choose multiple answers? I tried a countif in that scenario and it would not count. If I changed it to a single option or had it scan an open text field, then it would count.

KPH

To count things from a multi-select drop down, you will need @cell but you will also need a HAS function.

The syntax would be:

=COUNTIF({range}, HAS(@cell , "thing to look for within the multi select" ))

https://help.smartsheet.com/function/has

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.