Compare one value to visible column values

[akira1985]

Is it possible to compare one value to an entire column, considering only the visible cells?

I need to compare if one column is using a specific filter criteria.


Thanks.

[ragulduy]

You can use the specialcells method to return visible cells. So for example, to count how many times, "the" exists in column A considering visible cells:
worksheetfunction.countif(Columns(1).specialcells(xlcelltypevisible),"=the")

[FDibbins]

Hi and welcome to the forum

If you want VBA, then hopefully yudlugar's suggestion will work for you

You could probably do this with a regular formula though, using subtotal()

=SUBTOTAL(3,A2:A6) will count all the values, even hidden rows
=SUBTOTAL(103,A2:A6) will only count values in non-hidden rows

[ragulduy]

@ FDibbins - I had a look at that but couldn't work out a way to get it to do a countif type function - is there a way to do that where you can compare a specific value?

[jogg]

Hi

I am trying to resolve a similar problem - can I filter on one column and then count the words "Not applicable" in another column for non-hidden cells only?

Thanks

[FDibbins]

Hi jogg and welcome to the forum

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

[jogg]

Thanks for this advice, but I can't find what I click to create a new question - how do I do this?

[FDibbins]

Close to the very top of this thread, click the button called FORUM, then pick a forum and the Post New Thread

Thanks