Ask Your Question

How to count number of cells that contain a specific criteria from a filtered list [closed]

asked 2016-08-04 16:32:04 +0100

Tom Scogg gravatar image

updated 2020-07-20 11:28:09 +0100

Alex Kemp gravatar image

Hi all,

I want to be able to display the number of times a specific word/criteria shows up in a filtered column. I am currently trying to use SUMPRODUCT

=SUMPRODUCT(G2:G1000="Fully Comprehensive")

The result that comes back from the formula is 11. This is technically correct as this is actually the total number of cells within that specified range that contain the criteria "Fully Comprehensive". However, I have set my filter option on a different column and there are 3 rows that have been filtered accordingly and there is only one cell in the range that currently contains "Fully Comprehensive". Therefore I want to see '1' appear as a result of this formula and then as I add more data to this spreadsheet and continue to filter I want to see this number adjust accordingly.

Any help gratefully received.


edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-07-20 11:28:17.776353


You shouldn't expect a standard function to apply a filter on ranges passed to it. What should we do if we want the function to simply obey as written down? In most cases values made invisible by a filter will still be needed for evaluations.

Lupp gravatar imageLupp ( 2016-08-04 19:24:38 +0100 )edit

2 Answers

Sort by » oldest newest most voted

answered 2016-08-04 20:20:31 +0100

mark_t gravatar image

I looked at this earlier but the only functions I knew that check if a row is visible is the SUBTOTAL and AGGREGATE functions but could not use directly for your purpose. Reading Lupp's answer reminded me of a misuse of the SUBTOTAL function to check if the row of a table is visible, then you can use a helper column in combination with COUNTIFS to count only the visible occurrences.

Attached example

edit flag offensive delete link more


Great, I had seen this as a solution while I was searching for a answer but I disregard it. Thanks for re-directing me to it.

Tom Scogg gravatar imageTom Scogg ( 2016-08-06 17:16:27 +0100 )edit

answered 2016-08-04 18:19:20 +0100

Lupp gravatar image

updated 2016-08-04 19:19:45 +0100

There is a SUBTOTAL function made to be a frame for a limited number of functions accepting lists. It will restrict the application of the chosen function to the intersection of the basic range with the filter imposed upon that range. The function to apply by SUBTOTAL must be passed as an index.
In short: Database style, very special concerning the way of passing the 'FunctionParameter' to SUBTOTAL.
See attached example.

edit flag offensive delete link more

Question Tools

1 follower


Asked: 2016-08-04 16:32:04 +0100

Seen: 1,393 times

Last updated: Aug 04 '16