Help with counting # of unique cells that match criteria in another column

tiffany_smith

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!

Jennifer Kurtz

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!

tiffany_smith

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?

Jennifer Kurtz

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 )