ANCESTORS Function

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
    ]
)
  • 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