Is there a way to use a CountIF formula that would ignore hidden rows/cells?
Hello,
I am am trying to create a summary report that would count the number of "complete", "not started", and "In Progress" with only using the rows/cells that are filtered. I do not need to count the rows/cells that are hidden but when I use the COUNTIF function, it would also count the hidden rows/cells. Thank you.
Best Answer
-
A little correction on your formula:
=COUNTIFS(Status67:Status4304,AND(COUNT(ANCESTORS(@cell))=0,@cell="Complete"))
ANCESTORS cannot have a range for an argument, it's a cell or row.
And there's no way your COUNT(ANCESTORS()) returns a "Complete" value, so we have to divide that.
The "@cell" argument will make sure that both cases are tested on each cell of your range (who happens to be huge btw, so that makes lots of calculations for one sheet).
Hope that helped!
Answers
-
You would need to add the criteria that are used in the filter in the formula to include them in the formula.
Make sense?
Would that work?
I hope that helps!
Be safe and have a fantastic week!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Hi @Andrée Starå ,
Thank you for your response.
Would you be able to give me an example of how to do that?
Also, Would you be able to help with a COUNTIF formula that would only count the Parent Rows?
Thank you,
Johnathon S.
-
You would then need to add this criteria in the COUNTIFS function:
[Main Column], COUNT(ANCESTORS())=0 if you want to only include lines that are only parents (thus those that are on the top of the hierarchy).
otherwise go with:
[Main Column], COUNT(CHILDREN())>0 if you want to include lines that are parent or parent/child (thus only removing those that are at the bottom of the hierarchy).
-
Thank you for your response. I have tried the first method and it didn't work. I'm not sure if I did it correctly or not. Here is what I inputed.
=COUNTIFS(Status67:Status4304,COUNT(ANCESTORS([Task Name]67:[Task Name]4304))="Complete")
Here is exactly what I want the results to be. I want to count all the cells in the Status Column that say "Complete" if the are on the Parent Row. Its essentially two separate columns that I am working with to get my result.
Thank you,
Johnathon S.
-
A little correction on your formula:
=COUNTIFS(Status67:Status4304,AND(COUNT(ANCESTORS(@cell))=0,@cell="Complete"))
ANCESTORS cannot have a range for an argument, it's a cell or row.
And there's no way your COUNT(ANCESTORS()) returns a "Complete" value, so we have to divide that.
The "@cell" argument will make sure that both cases are tested on each cell of your range (who happens to be huge btw, so that makes lots of calculations for one sheet).
Hope that helped!
-
You're more than welcome!
I saw that David answered already!
Let me know if I can help with anything else!
Best,
Andrée
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!