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?
Best Answers
-
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
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
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
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
Answers
-
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
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
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"))
-
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
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
The main column I am referencing is data type "Date". The second column is "RYGG Balls".
-
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!
-
Perfect. Glad you found a solution. Thank you for contributing to the Community.
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
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.
-
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
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
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.
-
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
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
That did the trick! I should be able to finish up everything now with slight modifications. Thanks for your help Mark!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 463 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!