COUNTIFS to find unique values across two multi select dropdown columns?

DavidB.

Hi all, 

I'm looking for some help with a formula that will do the following (see screenshot): for every different "Person x" that appears in the Names column (multi select dropdown), count the total number of unique items that appear on rows wherever Person x is listed. Items are in another multi select dropdown column.

The intended result is shown in the last column (# different items) of the screenshot below. Here the number has been calculated and entered manually for illustration. Having searched the community forum for similar questions and tried a range of a formulas and functions in combination I haven't made any progress, so any suggestions would be greatly appreciated!

Many thanks,

David

Genevieve P.

Hi @DavidB.

There may be a more succinct way to do this, but I have a solution that may work for you... it will depend if you know all the different Item names already or not, as you'll need to type them into the formula.

Step 1: Counting instances one Item & Name

You could use a COUNTIF formula to count how many times Item A appears with the person in that row. Notice that I'm using the HAS function for both the Items column and the Names column because they're both Multi-Select columns.

COUNTIFS(Names:Names, HAS(@cell, [....]@row), Items:Items, HAS(@cell, "Item A"))

Step 2: Changing the count to be 1, to eliminate duplicates

Next we'll adjust the formula by adding an IF statement to say, if the count of this specific item with this person is greater than (or equal to 1), meaning there's a match for those two values, return just the number 1.

=IF(COUNTIFS(Names:Names, HAS(@cell, [....]@row), Items:Items, HAS(@cell, "Item A")) >= 1, 1)

Step 3: Add together each item with +

That formula is just looking for Item A. We can copy/paste this and change "Item A" to "Item B", and add the two statements together with a + to return either 0, 1, or 2. Do this with all of the item names.

For your example image above, the final formula would be as follows:

=IF(COUNTIFS(Names:Names, HAS(@cell, [....]@row), Items:Items, HAS(@cell, "Item A")) >= 1, 1) + IF(COUNTIFS(Names:Names, HAS(@cell, [....]@row), Items:Items, HAS(@cell, "Item B")) >= 1, 1) + IF(COUNTIFS(Names:Names, HAS(@cell, [....]@row), Items:Items, HAS(@cell, "Item C")) >= 1, 1) + IF(COUNTIFS(Names:Names, HAS(@cell, [....]@row), Items:Items, HAS(@cell, "Item D")) >= 1, 1) + IF(COUNTIFS(Names:Names, HAS(@cell, [....]@row), Items:Items, HAS(@cell, "Item E")) >= 1, 1)

Since you've nicely set up your chart to have the person's name in the [....] column, you can just drag-fill this down the formula column and it will auto-populate with the correct numbers.

Here are some Help Center articles I used to build this: HAS Function / COUNTIF Function / IF Function / @row and @cell Functions / Formula Operators

Let me know if this makes sense, or if I can clarify anything further!

Cheers,

Genevieve

Genevieve P.

Hi @DavidB.

There may be a more succinct way to do this, but I have a solution that may work for you... it will depend if you know all the different Item names already or not, as you'll need to type them into the formula.

Step 1: Counting instances one Item & Name

You could use a COUNTIF formula to count how many times Item A appears with the person in that row. Notice that I'm using the HAS function for both the Items column and the Names column because they're both Multi-Select columns.

COUNTIFS(Names:Names, HAS(@cell, [....]@row), Items:Items, HAS(@cell, "Item A"))

Step 2: Changing the count to be 1, to eliminate duplicates

Next we'll adjust the formula by adding an IF statement to say, if the count of this specific item with this person is greater than (or equal to 1), meaning there's a match for those two values, return just the number 1.

=IF(COUNTIFS(Names:Names, HAS(@cell, [....]@row), Items:Items, HAS(@cell, "Item A")) >= 1, 1)

Step 3: Add together each item with +

That formula is just looking for Item A. We can copy/paste this and change "Item A" to "Item B", and add the two statements together with a + to return either 0, 1, or 2. Do this with all of the item names.

For your example image above, the final formula would be as follows:

=IF(COUNTIFS(Names:Names, HAS(@cell, [....]@row), Items:Items, HAS(@cell, "Item A")) >= 1, 1) + IF(COUNTIFS(Names:Names, HAS(@cell, [....]@row), Items:Items, HAS(@cell, "Item B")) >= 1, 1) + IF(COUNTIFS(Names:Names, HAS(@cell, [....]@row), Items:Items, HAS(@cell, "Item C")) >= 1, 1) + IF(COUNTIFS(Names:Names, HAS(@cell, [....]@row), Items:Items, HAS(@cell, "Item D")) >= 1, 1) + IF(COUNTIFS(Names:Names, HAS(@cell, [....]@row), Items:Items, HAS(@cell, "Item E")) >= 1, 1)

Since you've nicely set up your chart to have the person's name in the [....] column, you can just drag-fill this down the formula column and it will auto-populate with the correct numbers.

Here are some Help Center articles I used to build this: HAS Function / COUNTIF Function / IF Function / @row and @cell Functions / Formula Operators

Let me know if this makes sense, or if I can clarify anything further!

Cheers,

Genevieve

Paul Newcome

I am going to agree with @Genevieve P's solution.

I was really hoping to find a way to combine COUNTM, DISTINCT, and COLLECT, but the COUNTM and DISTINCT functions don't seem to work together to be able to get a count of distinct selections (at least not that I have figured out yet).

Genevieve P.

Thanks for adding that, Paul - I was initially trying to use COUNTM but couldn't quite figure it out with DISTINCT, either.

Paul Newcome

I was able to get a formula that didn't throw an error, but it didn't seem as if the DISTINCT made a difference.

=COUNTM(COLLECT(

Provided the same results as

=COUNTM(DISTINCT(COLLECT(

I might revisit it and do some more testing later today, but I think the issue is that COUNTM isn't actually parsing out the data but maybe counting the line breaks to work.

DavidB.

Hi @Genevieve P,

Thank you so much for solving my problem! This is a real masterclass in formula writing for me and it's really helpful how you've broken the solution down to aid understanding (although I can't say I've quite got my head around how Step 1 works exactly...). And thank you Paul for also giving this your consideration, really appreciate it.

My actual use case is a bit messier than the above example, I have about 100 different "items" to build into this formula (and get it to work across sheets) but I look forward to giving this a go. Is there any limitation to how long a formula can be in Smartsheet, because this one is going to be a monster!

Thanks again,

David

Paul Newcome

4,000 characters including spaces is the limit.

I am going to see if I can figure out an alternative possibly using some helper columns because 100 different Items is going to be miserable to put together. Ugh.

DavidB.

Thanks Paul, this one if definitely going to exceed the limit. Nothing wrong with a few helper columns though.

Could Genevieve's formula be applied to subsets of my Items list to stay within the character limit, and then the results for each subset summed to get to the unique items total?

Paul Newcome

You can break this down into multiple formulas such as Items 1 - 10 in one formula plus 11 - 20 in another.

Paul Newcome

How many people do you have that you need counts for?

DavidB.

There are 33 people I need counts for and 109 items (~18 characters per item name).

Was wondering if I could use Excel's concatenate function to more easily compile the text of the Smartsheet formula?

Paul Newcome

I am not sure about using Excel for that (I've never tried it). I have a couple of ideas I want to test out to see if we can make this a little more efficient, but in the meantime, I am going to suggest sticking with @Genevieve P's solution spread out across a few columns.

Genevieve P.

Hmm yes 100 items would be a lot. You could break it into 4 different hidden columns (each with 25 items) and a final column with the total sum across those columns... but I understand that's a lot.

At least you won't have to add in the people, though! The only thing that will need to change are the item names. To make it a bit easier, you could have the 100 items listed somewhere and use absolute references to lock onto the cell with the correct item, instead of typing the name:

=IF(COUNTIFS(Names:Names, HAS(@cell, [....]@row), Items:Items, HAS(@cell, [Item List]$1)) >= 1, 1) + IF(COUNTIFS(Names:Names, HAS(@cell, [....]@row), Items:Items, HAS(@cell, [Item List]$2)) >= 1, 1)

etc.

Then you wouldn't need to re-type the item names but just adjust what row number the Item is listed in and adjust the number. This could keep your character count down, too.

Paul Newcome

@Genevieve P That's kind of along the lines of where I have been trying to work. Using a table for reference. I have gotten so close a couple of different ways, but nothing that works perfectly.

One thing I am thinking could work (I know it CAN work, but I don't know if @DavidB. is interested in it) is a table where each person has their own column and each item their own row. If you replicate each person's name in the top row, you could use cell references to dragfill a generic (and basic) formula across and down. From there you can get a count of how many of each item and how many different items for each person rather easily using a couple of generic SUM functions.

Once you pull this data, there are a ton of possibilities for additional metrics. Depending on your needs, you could even forgo the "X" and put the count for how many times Item A pops up for David to further expand your possibilities.

I know it would take a fair amount of initial setup doing 33 columns (one for each person), but the formulas could be pretty simple (short and generic and dragfillable) and it would provide for a lot of different metrics that will update automatically as the sheet is built out.

Genevieve P.

Oh that's a great idea!!! Loving the organization.

I can definitely see different metrics and widgets being pulled from a sheet like that into a dashboard. Interested to see what @DavidB. thinks!

Paul Newcome

Aside from the metrics possibilities, I like it for the formula simplicity (and lengths).

Using cell references and locking in certain aspects allows you to dragfill for the majority of the chart.

To fill the main matrix, just enter this into David2 and dragfill down and across:

=IF(COUNTIFS($Names:$Names, FIND(David$1, @cell) > 0, $Items:$Items, FIND($[Item (Table)]@row, @cell) > 0) > 0, COUNTIFS($Names:$Names, FIND(David$1, @cell) > 0, $Items:$Items, FIND($[Item (Table)]@row, @cell) > 0))

Which could be simplified even further if you don't mind showing counts of zero.

Then across the bottom is a basic

=SUM(David:David)

dragfilled over

And the Item totals is another basic

=SUM(David@row:Paul@row)

dragfilled down.

Granted the solution of adding the COUNTIFS together is relatively simple too, but it can get rather expansive and requires a lot of typing and very specifically customized formulas.

Building out the table allows for very easy expansion in either direction (more items and/or more people) and (as has been stated before) opens up a lot of other possible metrics.

To adapt the tables to fit the exact needs of getting a count of how many different items per person, you could use a basic COUNTIFS of

=COUNTIFS(David:David, "X")

or

=COUNTIFS(David:David, @cell > 0)