# Changing formula to work with visible (non-filtered) cells only

Below are two formulas that work but unsure how to change these so they do the same thing to only visible cells:
=SUM(IF(FREQUENCY(O10:O2010,O10:O2010)>0,1))
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
Hi,

pls try

' and
=SUM(IF(FREQUENCY(IF(SUBTOTAL(103,OFFSET(O10,ROW(O10:O2010)-ROW(O10),)),O10:O2010),O10:O2010)>0,1,0))

Regards

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
This one:
=SUM(IF(FREQUENCY(IF(SUBTOTAL(103,OFFSET(O10,ROW(O10:O2010)-ROW(O10),)),O10:O2010),O10:O2010)>0,1,0))
Has a error of #Value!

The first one works perfect!, Thanks!
Commented:
HI,

it's an array formula

Use Ctrl-Shift-Enter
Commented:
I don't think the SUMPRODUCT version suggested by Rgonzo1971 will be guaranteed to work in all circumstances. It only works if each set of duplicates is either completely hidden or completely visible, if not you may get fractional results which obviously don't make sense, e.g. if the whole range AD10:AD2010 is empty except for "x" in the first two rows.....and one of those rows is visible and the other is not you will get the result 0.5 rather than 1

I suggest this version to count the number of different and visible

confirmed with CTRL+SHIFT+ENTER

regards, barry
Author Commented:
Both of these when I paste into the cell cause a #Value! msg to appear in the cell :
=SUM(IF(FREQUENCY(IF(SUBTOTAL(103,OFFSET(O10,ROW(O10:O2010)-ROW(O10),)),O10:O2010),O10:O2010)>0,1,0))