countif only visible cells

**nicko54** · 02-15-2010, 10:27 AM

I have a countif to sum the amount of times "Yes" appears in column D.

when i hide a range of rows the formula still counts the hidden cells.

is there anyway i can do a count of all the "Yes"s in column D and not include the hidden rows?

Many thanks,

Nicko

**DonkeyOte** · 02-15-2010, 10:29 AM

If you use an adjacent column, eg:

E2: =SUBTOTAL(3,D2)
copied down for all rows where say D2 onwards holds your values of interest (being filtered)

You should find you can then use:

=SUMIF($D$2:$D$1000,"Yes",$E$2:$E$1000)

The other alternative would be to use a relatively complex & volatile SUMPRODUCT formula.

**nicko54** · 02-15-2010, 11:43 AM

this doesnt seem to be working for me...

the formula still counts hidden cells.

i have attached an example to show the problem.

any ideas?

**nicko54** · 02-15-2010, 12:30 PM

i found an answer using an UDF on another site that works well...

http://www.mrexcel.com/forum/showthread.php?t=45637

**DonkeyOte** · 02-15-2010, 01:14 PM

Thanks for posting the link to the UDF.

Regards the attachment you posted - there were no filters in place so I confess I'm unsure as to how you validated that it did not work... if I add filters to C5:E5 and filter say by a few "connections" I get the expected results.

If you are altering visibility without using a Filter (ie you're physically hiding rows) then you would need to use:

E6: =SUBTOTAL(103,D6)
copied down

ie 103 rather than 3

that said the 100 numbers are not available before XL2003.

(given you don't outline your version in your profile it's not clear if the above will be an issue for you or not)