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

English
,
#1

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.

cheers

#2

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.

#3

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.

#4

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

#5

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.

#6

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.