COUNTIF using Count(Ancestors)

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?

Find more posts tagged with

Formulas

Accepted answers

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

All comments

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,

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!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.