Use only parent rows in a referenced sheet

John Knipper

I'm trying to count the number of times a text string is found in a different sheet in columns of the same name, but I only want to count if the name is in a parent row of the referenced sheet. The formula below is #unparseable. What is wrong with it?

=COUNTIF({Sheet1 Names}, CONTAINS(Names@row, @cell), {Sheet1 Names}, count(ancestors(@cell))=0)

Thank you, John

Paul Newcome

A couple of things...

To have multiple range/criteria sets, you will have to use COUNTIFS instead of COUNTIF. Additionally, hierarchy based functions cannot be used in cross sheet formulas.

You are going to want to insert a column on the sheet containing the source data, put your

=COUNT(ANCESTORS())

there, then reference this new column in your COUNTIFS.

=COUNTIFS({Sheet1 Names}, CONTAINS(Names@row, @cell), {Sheet1 New Column}, @cell = 0)

Paul Newcome

A couple of things...

To have multiple range/criteria sets, you will have to use COUNTIFS instead of COUNTIF. Additionally, hierarchy based functions cannot be used in cross sheet formulas.

You are going to want to insert a column on the sheet containing the source data, put your

=COUNT(ANCESTORS())

there, then reference this new column in your COUNTIFS.

=COUNTIFS({Sheet1 Names}, CONTAINS(Names@row, @cell), {Sheet1 New Column}, @cell = 0)

John Knipper

I wasn't aware that hierarchy-based functions couldn't be used in a cross sheet formula. (I had considered creating the helper column for the hierarchy in the source sheet, but I was hoping not to create another column.) Thanks!😃

Paul Newcome

Happy to help. 👍️