Multiplying the Values of My Ancestors

Trent T

Hello, has anyone found a way to multiply the values of the Ancestors for a specific cell together?

For example, I'm trying to get the total amount of 31445A113 required for the assembly A-341-10-1-0999.

I was hoping to just use the Ancestors formula to multiply the quantity for A-341-10-1-0999 (x2) but the quantity for A-341-10-2-005 (x3) .

Then I would multiply by the quantity for 31445A113 (x4) to get the total amount needed (24).

Naeem Ejaz

Smartsheet’s built-in ANCESTORS() function only returns a list of values (or text) from parent rows—it doesn’t perform calculations like multiplication across those values. That’s why you can’t directly multiply ancestor quantities using ANCESTORS().

• ANCESTORS() returns an array, but Smartsheet formulas don’t allow array math (e.g., multiplying all returned values).

• You need a way to cascade quantities down the hierarchy.

Helper Column for Cumulative Quantity

1. Add a column called Cumulative Qty.
2. =IF(COUNT(ANCESTORS([Quantity]@row )) = 0, [Quantity]@row ,[Quantity]@row * PARENT([Cumulative Qty]@row ))