This function is designed for working with data in row hierarchies.

Returns the values of all cells that are ancestors of the specified cell. If no cell is specified, the cell the function is called within is used as the reference.


Sample Usage







  • cell - [OPTIONAL] The cell to use as a reference for retrieving all ancestor cell values. If no cell is provided, this function returns all ancestor cell values of the cell it is called from.



COUNT(ANCESTORCELLS()) returns the number of ancestor cells above the current cell in the hierarchy.

COUNT(ANCESTORCELLS(A30)) returns the number of ancestor cells below cell A30 in the hierarchy.

MAXA(ANCESTORCELLS(A30)) returns the maximum value of all ancestor cells below cell A30 in the hierarchy.



  • ANCESTORCELLS will be recalculated on indent/outdent changes, create-row and delete-row actions, etc.
  • ANCESTORCELLS returns an array of values.
  • Unlike the PARENTCELL function, ANCESTORCELLS returns all cell values of cells above the specified cell in the hierarchy.