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))
=SUMPRODUCT((AD10:AD2010<>"")/COUNTIF(AD10:AD2010,AD10:AD2010&""))
Thanks in advance
dgd1212Asked:
Who is Participating?
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.

Rgonzo1971Commented:
Hi,

pls try

=SUMPRODUCT(SUBTOTAL(103,OFFSET(AD10,ROW(AD10:AD2010)-ROW(AD10),0)),(AD10:AD2010<>"")/(COUNTIF(AD10:AD2010,AD10:AD2010&"")))
' and
=SUM(IF(FREQUENCY(IF(SUBTOTAL(103,OFFSET(O10,ROW(O10:O2010)-ROW(O10),)),O10:O2010),O10:O2010)>0,1,0))


Regards
0

Experts Exchange Solution brought to you by

Your issues matter to us.

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

Start your 7-day free trial
dgd1212Author 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!
0
Rgonzo1971Commented:
HI,

it's an array formula

Use Ctrl-Shift-Enter
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

barry houdiniCommented:
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

=SUM(IF(FREQUENCY(IF(SUBTOTAL(103,OFFSET(AD10,ROW(AD10:AD2010)-ROW(AD10),0))*(AD10:AD2010<>""),MATCH(AD10:AD2010,AD10:AD2010,0)),ROW(AD10:AD2010)-ROW(AD10)+1),1))

confirmed with CTRL+SHIFT+ENTER

regards, barry
0
dgd1212Author 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))
=SUM(IF(FREQUENCY(IF(SUBTOTAL(103,OFFSET(AD10,ROW(AD10:AD2010)-ROW(AD10),0))*(AD10:AD2010<>""),MATCH(AD10:AD2010,AD10:AD2010,0)),ROW(AD10:AD2010)-ROW(AD10)+1),1))
0
Rgonzo1971Commented:
entered with CTRL+SHIFT+ENTER?

the formula should be afterwards between curly braces (do not try to type them)
0
dgd1212Author Commented:
Rgonzo1971,
It finally dawned on me what/how to do that CTRL+SHIFT+ENTER. It Works!! Thank you!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.