Counting filtered data

David Noland

Hi - I have a question. I want to count the number of rows NOT BLANK after I filter the rows. Currently, the counting formula counts all of the rows even the ones that are filtered out. This seems rather simple but I could not seem to figure it out.

Thanks in advance for your help!

David

David Joyeuse

Hi @David Noland

You need to use the COUNTIF formula and use your filtered options as range & criteria in the COUNTIF function.

Hope it helped!

David Noland

Thanks for your help but this did not work.

This picture shows how it should work.

Notice that the header column in row 5 has the formula. The result is 2 indicating that when the filter is set to "Level 2", then there are two rows under the Engineering header that meet that filter.

The problem is, of course, that it returns the value of 2 always. When I change the filter to Level 1, it returns this.

Note that the header value stays at 2 but there is more than 2 rows. Same with Level 3 below.

While it returns the value of 2 and there are 2 rows, it is still counting the Level 2 rows.

This is my problem. I need the number in row 5 Sub Process # to reflect the number of child rows for each filter.

Thanks!

David

David Noland

Bump to the top

David Joyeuse

@David Noland

That's not exactly quite I thought when I read your first post.

So If I understand you correctly, you want the formula to change when you change the filter option right?

I don't have much answer to provides right now, because your formula is independant from any filter options (that are just a way to display stuff from the sheet basically).

What comes first to mind is having an helper cell that is displaying the filter option (not sure how to do it automatically though).

Then change your CONTAINS formula like this:

=COUNTIFS(CHILDREN([Audit Level]@row), CONTAINS($[Helper Column]$1, @cell))

Hope it helped!

PleaseHelpJay

Hello! I just found this and I'm trying to do something similar to get a rolling count of different phrase occurrences in a column.

Is there a way to integrate a filter into a formula? I wasn't sure what @David Joyeuse, you meant with your very first response.

Best,

Jay