Subtotals with countif not possible

**germanjulian** · 06-13-2007, 01:44 PM

sight

I have 2 columns and hundreds of rows, in the column I have "Yes" or "No".

I have created an autofilter on column ("country") and I want to know how many "Yes" and "No" answer I get for each column when I apply the filter, since countif ignores hidden rows I thought I can use subtotals but subtotals does not support countif....

ie:
Country Yes/No Column
IN No
IN No
IN No
GB No
GB No
GB Yes
GB Yes
GB Yes

when I apply the filter "IN" I only want to count "IN" "yes and "no" values.
so I get IN = 3 no and 0 yes... not 3 yes and 5 no due to countif ignoring the filter.

can somebody help?

**NBVC** · 06-13-2007, 02:12 PM

Assuming your table is in A1:B9, then for "No" try:

Code:

=SUMPRODUCT(SUBTOTAL(3,OFFSET($B$2:$B$9,ROW($B$2:$B$9)-MIN(ROW($B$2:$B$9)),,1)),--($B$2:$B$9="No"))

adjust ranges to suit.

Change "No" to "Yes" in last argument for count of "Yes"

**Philster** · 03-20-2010, 07:42 PM

This works, except for cells with empty values that you are trying to count, too.

I enclose an example

in C1 is an example of the solution formula, testing for empty cell. doesn't work.
in D1 is an example of the solution formula, testing for "N". works.

My intent is to put both into an IF statement, to alert me if the long list contains a specific value.

Can anybody assist? that is, make it work for "" and non-"" values?