Is there a way to use a CountIF formula that would ignore hidden rows/cells?

John Sisounthone · 08/11/20

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.

David Joyeuse · 08/12/20

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!

Andrée Starå · 08/11/20

Hi @John Sisounthone

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!

John Sisounthone · 08/12/20

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.

David Joyeuse · 08/12/20

Hi @John Sisounthone

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).

John Sisounthone · 08/12/20

Hi @David Joyeuse

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.