Help with counting # of unique cells that match criteria in another column
Hi All! I've tried this formula so many different ways and I can't quite get it work. Here is my source data + a description of what I'm trying to do:
I would like to count the # of unique PMs Assigned to each Marketing Imperative. For example, each row in my data sheet is its own project (I left this column off for data privacy reasons) and each of those projects is mapped to a Marketing Imperative (listed in the last column). A single PM may be assigned to multiple projects - as you can see from the data there are 3 unique PMs assigned to projects mapped to our "Be the Destination Team" imperative ("First PM", "Second PM", and "Seventh PM"). So I'm trying to write a formula that can identify the unique PM names across all the project rows that are mapped to the "Be the Destination Team" imperative (which should return the #3). I should also note that the data I'm referencing is on a different sheet labeled "Project Coverage".
All of my attempts are returning an UNPARSABLE error, but I can't seem to figure out where the error is in my syntax, even when I've tried process of elimination, tweaking a single thing in the syntax one at a time. Here's what I started with:
=COUNTIFS(DISTINCT(COLLECT{Project Coverage Range [PM Assigned]}, {Project Coverage Range [Marketing Imperative}, "Be the Destination Team"))
Can anyone advise on what I'm doing wrong? TIA!
Answers
-
Hi!
I don't think you need the "Count Ifs" function here, as the Collect function will cover any criteria you'd need to enter. You could use Count(Distinct(Collect(
That would give you a number of distinct PMs assigned to each imperative. (Your syntax looks right to me - i think you'd only need to change the CountIfs to a Count in your formula above!)
If you wanted to pull in the actual names of the PMs, you could use JOIN(Distinct(Collect(
That would look like =JOIN(DISTINCT(COLLECT({Project Coverage Range [PM Assigned]}, {Project Coverage Range [Marketing Imperative}, "Be the Destination Team")), "add whatever delimiter you want here"🙂
hope that helps!
-
Thanks for the quick response Jennifer! That suggestion did get the formula to parse but it returned 1 instead of 3. Is it possibly stopping at the first match instead of reading the whole range?
-
Hm - I'd suggest checking your ranges to make sure they're correct, including that you're referencing the entire "Marketing Imperative" column.
Hopefully that helps to troubleshoot! (Formulas are so fun, amirite :D )
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 464 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!