COUNTIFS with a multiple drop down field

I'm struggling with some syntax. I have the following fields:

I want to count status by team lead. The team lead is a dropdown allowing multiple entries, status is a dropdown with single entry. I found that it counts Joe, but can't find Jane.

I have two references to this table - {Retail - Status} and {Retail - Lead)

My original syntax s as follows:

=COUNTIFS({Retail - Status}, "Red", {Retail - Lead}, "Jane Jones") – Picks up Joe, but will not pick up Jane

Tried some variations:

=COUNTIFS (CONTAINS ("Green", {Retail – Status}, "TRUE", "FALSE"), CONTAINS ("Jane Jones", {Retail – Lead}, "TRUE", "FALSE").

=COUNTIFS(FIND("Jenny O’Rourke", {Retail-Lead}) > 0, {Retail-Status}, "Green")

Any assistance is appreciated

Find more posts tagged with

Accepted answers

Ryan Sides

@Jen S.

Hi Jen, you'll need to use the HAS function on multiselect dropdowns and multiselect Contacts columns:

=COUNTIFS({Retail - Lead}, HAS(@cell, "Jane Jones"), {Retail - Status}, "Red")

Please let me know if that works for you!

-Ryan

All comments

Ryan Sides

@Jen S.

Hi Jen, you'll need to use the HAS function on multiselect dropdowns and multiselect Contacts columns:

=COUNTIFS({Retail - Lead}, HAS(@cell, "Jane Jones"), {Retail - Status}, "Red")

Please let me know if that works for you!

-Ryan

Jen S.

Thank you, that worked

Ryan Sides

@Jen S. Whoop! Glad it did :) - Ryan

Jen S.

@Ryan Sides So, I spoke too soon on this. I'm getting weird errors. The first query below is a summary sheet field. The second query is where I'm trying to pull the same info into a summary sheet since I have 7 names with 4 different statuses which creates 112 different combinations.

I would really rather not put 112 summary fields in to capture this information.

Summary query:

=COUNTIFS([Team Lead]:[Team Lead], "Andy Becker", Status:Status, "Gray")

Separate Sheet Query with the cross references

=COUNTIFS({Retail - Lead}, HAS(@cell, "Andy Becker"), {Retail - Status}, HAS(@cell,"Gray"))

The first query returns 10 which is correct, the second query returns 14. In fact every query (different names, statuses) are all returning 14.

Genevieve P.

Hi @Jen S.

I would recommend going the Summary Sheet route with this!

You could then have a Contact Column with a single contact listed down the column in each cell. Then you can reference this cell in your formula instead of writing out the name:

HAS(@cell, "Andy Becker") - changes to - HAS(@cell, [Contact Column]@row)

Then for the Status column you don't need the HAS function, as it will only ever have one selection at a time.

Try this:

=COUNTIFS({Retail - Lead}, HAS(@cell, [Contact Column]@row), {Retail - Status}, "Gray")

Then in a second column, you could count the other status colours:

=COUNTIFS({Retail - Lead}, HAS(@cell, [Contact Column]@row), {Retail - Status}, "Green")

Cheers,

Genevieve

Ryan Sides

@Jen S. Hi Jen, did you get this one figured out? Sorry for my delay in replying I was out last week.

@Genevieve P. is spot on in her reply!

-Ryan

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.