Welcome to the community Nic, and great question! This is something we plan to have a much easier solution for.
In the meantime, the best way to do this is to write a formula or column formula to count the number of unique items within a cell by parsing it and doing some calculations.
When you reference a related row cell in a formula, you will get a comma separated string back. For example, in your screenshot, the cell formula =CONCAT(I3) would give you 334, 553. So you can use the LEN, TRIM, and SUBSTITUTE functions to write a column formula that you can use for your column J to get the number of unique related row values in column I.
This column formula should work for your example:
LEN(TRIM(I))-LEN(SUBSTITUTE(TRIM(I),“,”,“”))+IF(LEN(I)>0,1,0)
Unfortunately you can’t use traditional COUNT or COUNTA spreadsheet functions to do this because these functions are only designed to tell us whether cells in a given range have values in them or not. They don’t tell us how many values a particular cell has within it.
So we need to treat each cell’s content as a string and write a formula to check how many unique values exist within it. I created a simpler example to demonstrate this with a worksheet for Fruits and a worksheet for Meals in which I ate those fruits (disclaimer: I don’t actually eat this much fruit, it’s just the first example that came to me):
Often when faced with these kinds of challenges, my default hack is to do a Google search, such as “count comma separated values in a cell”. Since Spreadsheet.com works the same as a traditional spreadsheet, there’s often an answer out there in some form:
count comma separated values in a cell - Google Search
Some folks on the team may have a simpler solution for you. More importantly, we have plans to make this kind of thing far simpler in the future when working with Spreadsheet.com data types.
