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:


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 works the same as a traditional spreadsheet, there’s often an answer out there in some form:

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 data types.



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



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