ANCESTORCELLS with COUNT doesn't work with IF

I am trying to write some logic to display something for a parent in a hierarchy, but something different for its children. When I set the formula to just COUNT(ANCESTORCELLS()) the values display as expect: 0 for the parent, 1 for the children.

However, when I try to use IF(COUNT(ANCESTORCELLS())=0,"p","c") both parent and children display β€œp”.

In the image below, column F - Count displays just the count and column G - DTE uses the IF logic.

I also tried using ANCESTORCELLS(A), no change. I noticed the same effect with PARENTCELLS().

Apologies if it’s user error here.

1 Like

I should note this is occurring when using the Column Formula field.

I can get things to work when setting field type to Automatic and using the formula in each cell.

Request you to use COUNTA instead of COUNT. COUNT() looks specifically for numeric values in the referenced data-set.

1 Like

Thanks, that is definitely working better for me now. That solves my problem. But I think there might still be something else up here.

Both COUNTA and COUNT return the same values: 0 for parents, 1 for children. It’s like COUNT returns a number, but COUNTA returns a string. But I’ve tested that isn’t the case.

Interestingly enough, if I move the IF formula to a separate column, then COUNT works as expected.

image

The reason why COUNT is not working in combination with IF formula is, for child cells ANCESTORCELLS() is returning data-set {β€œp”}, where β€œp” is the evaluated value of the parent cell IF formula in the same column. This being a string literal is ignored by COUNT but is picked up by COUNTA.

However, COUNT used individually, returns {0} where again 0 is the value of the parent cell COUNT formula in the same column. Hence the count of a child-cell is returning 1 as it works against data-set {0} with a single number literal in it.

Basically, ANCESTORCELLS if not passed an argument, take the current cell as reference for which ANCESTORS need to be evaluated (i.e. ancestors within the same column).

2 Likes

Ah that makes perfect sense. Thanks for the explanation!

1 Like