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
Best Answer
-
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
Answers
-
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
-
Thank you, that worked
-
@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.
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
@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
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 454 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!