ANCESTORS Function | Smartsheet Learning Center

Used within another function to reference all of the ancestors in a cell’s hierarchy.

Sample Usage

COUNT(ANCESTORS([Task Name]4))

Syntax

ANCESTORS(

)

reference
—[optional]
The cell for which you want the ancestors. If no cell is specified, the function references the ancestors of the current cell.

Examples

This example references the following sheet information:

Row #	Project	LEVEL	Item Number	Cost Per Unit	Due Date
1	- Men's Formal Button Down	0	MF01	$38.75	02/27/19
2	MF01 - Design Phase	1	DP	$25.00	02/19/19
3	-- MF01 - Assembly	1	A0	$13.75	02/27/19
4	Cutout Shirts	2	A001	$10.00	02/22/19
5	Sew Shirt	2	A002	$2.50	02/26/19
6	Add Buttons	2	A003	$1.25	02/26/19

Given the table above, here are some examples of using ANCESTOR in a sheet:

Formula	Description	Result(s)
=COUNT(ANCESTORS())	Count the amount of Ancestors for the cell containing the formula. This formula is within each row of the “Level” column.	Row one: 0 Row two & three: 1 Row four through six: 2
=SUM(ANCESTORS([Cost Per Unit]5))	Sum the Ancestors for row 5 in the “Cost Per Unit” column. It has two Ancestor rows (row 3 & 1).	$52.50
=INDEX(ANCESTORS([Item Number]2), 2) + " - " + "Design Phase"	Return the Ancestor of the value in the “Item Number” column, row 2. Then add the values “- Design Phase” to the result.	MF01 - Design Phase

Formula

Description

Result(s)

=COUNT(ANCESTORS())

Count the amount of Ancestors for the cell containing the formula.

This formula is within each row of the “Level” column.

Row one: 0

Row two & three: 1

Row four through six: 2

=SUM(ANCESTORS([Cost Per Unit]5))

Sum the Ancestors for row 5 in the “Cost Per Unit” column.

It has two Ancestor rows (row 3 & 1).

$52.50

=INDEX(ANCESTORS([Item Number]2), 2) + " - " + "Design Phase"

Return the Ancestor of the value in the “Item Number” column, row 2. Then add the values “- Design Phase” to the result.

MF01 - Design Phase