COUNTIF using Count(Ancestors)

Travis Myers

I am trying to use the COUNTIFS formula to return a count of items within a date range. The issue is a large number of the rows have indented/child rows that I do not want included in the count.

The formula I have tried is:

=COUNTIFS({FOLLOW UP Range 1},COUNT(ANCESTORS()=0)>DATE(2021,1,1){FOLLOW UP Range 1},COUNT(ANCESTORS()=0)<DATE(2021,1,31)))

Follow up range 1 is just the column containing the dates. This returns as unparseable. What did I mess up on?

Mark Cronk

Hi Travis,

Your formula had a paren out of place. Also confirm that the column this formula is formatted as text/number. Try:

=COUNTIFS({FOLLOW UP Range 5}, AND(@cell > DATE(2021, 1, 1), @cell < DATE(2021, 1, 31)), {FOLLOW UP Range 6}, @cell = "green")

Work?

Mark

Mark Cronk

Hi Travis,

The syntax for FIND is: =FIND( search_for, text_to_search) and returns the starting position of the search_for. To use FIND as criteria you format it as = FIND( search_for, text_to_search)>0 if you want the string or =FIND( search_for, text_to_search)=0 if you don't want the string.

In your formula it would be:

=COUNTIFS({FOLLOW UP Range 5}, AND(@cell > DATE(2021, 1, 1), @cell < DATE(2021, 1, 31)), {FOLLOW UP Range 6}, @cell = "green", {Insert Range} , FIND(@cell, text_to_search)>0)

Work?

Mark

Mark Cronk

Hi @Travis Myers,

I think to make this work you need to add a helper column [Level] with the formula = COUNT(Ancestors()) rather than embed that into your formula.

Then your formula would be:

=COUNTIFS({FOLLOW UP Range 1}, AND(@cell>DATE(2021,1,1) @cell<DATE(2021,1,31), {Level range}, @row=0)

Work?

Mark

Travis Myers

I tried that and added another reference to a column that is a status column. Red for incomplete, green for complete. So I subbed out the {Level range} with another reference (Follow up range 2). It returns invalid data type.

The exact formula I tried was:

=COUNTIFS({FOLLOW UP Range 5}, AND(@cell > DATE(2021, 1, 1), @cell < DATE(2021, 1, 31), {FOLLOW UP Range 6}, @cell = "green"))

Mark Cronk

Hi Travis,

Your formula had a paren out of place. Also confirm that the column this formula is formatted as text/number. Try:

=COUNTIFS({FOLLOW UP Range 5}, AND(@cell > DATE(2021, 1, 1), @cell < DATE(2021, 1, 31)), {FOLLOW UP Range 6}, @cell = "green")

Work?

Mark

Travis Myers

The main column I am referencing is data type "Date". The second column is "RYGG Balls".

Travis Myers

It worked! I had to delete out all my references as they were messed up somewhere. Once I cleaned those out and started over, it worked wonderfully. Thank you!

Mark Cronk

Perfect. Glad you found a solution. Thank you for contributing to the Community.

Mark

Travis Myers

I do have a slight spin off question to this. How would you add the FIND function to this? I would still need to count within the date range, but find cells that contain a specific word while once again only counting the parent rows.

Mark Cronk

Hi Travis,

The syntax for FIND is: =FIND( search_for, text_to_search) and returns the starting position of the search_for. To use FIND as criteria you format it as = FIND( search_for, text_to_search)>0 if you want the string or =FIND( search_for, text_to_search)=0 if you don't want the string.

In your formula it would be:

=COUNTIFS({FOLLOW UP Range 5}, AND(@cell > DATE(2021, 1, 1), @cell < DATE(2021, 1, 31)), {FOLLOW UP Range 6}, @cell = "green", {Insert Range} , FIND(@cell, text_to_search)>0)

Work?

Mark

Travis Myers

It works but returns 0 when it shouldn't. I am searching for the word "REBID" and there should be a handful within the date range I am searching. The cells contain a host of text, so I am only looking for that single word within it. The column I am looking in is the primary column and its data type is text/numbers so am unsure why its returning zero.

Mark Cronk

I see what happened. I reversed the FIND snytax in the example I gave you. FIND should be = FIND( search_for, text_to_search). Try:

=COUNTIFS({FOLLOW UP Range 5}, AND(@cell > DATE(2021, 1, 1), @cell < DATE(2021, 1, 31)), {FOLLOW UP Range 6}, @cell = "green", {Insert Range} , FIND("REBID", @cell)>0)

Work?

Mark

Travis Myers

That did the trick! I should be able to finish up everything now with slight modifications. Thanks for your help Mark!