COUNT for cell values in Related Row

I’m creating a HRM workbook and using a Related Row data type between two sheets to list all our employees ID who report to a specific customer manager (I - EMPL iD).

In J - EMPL HC / PO, I have selected the data type: f(x) Column formula and entered COUNTA(I). However, the result seems to only recognize the first value in Column I (334). I’ve tried using different COUNT formulas but none seem to produce a value of 2.

Any suggestions? I know Airtable has an @ Rollup feature that will count the number of values within a particular cell.

01 I Result of Column Formula I COUNTA

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:

https://www.google.com/search?q=count+comma+separated+values+in+a+cell

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.

3 Likes

Matt,

Much appreciated! The information and example you provided makes sense and worked perfectly.

Spreadsheet.com rocks!

-Nic

4 Likes

Hi Nic_Harbour,

The Related Row Rollup feature is now available in the product with the latest upgrade.
Please try out to resolve your above query.
For Additional information,

1 Like