asked on

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

ASKER CERTIFIED SOLUTION

Rgonzo1971

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dgd1212

ASKER

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!

Rgonzo1971

HI,

it's an array formula

Use Ctrl-Shift-Enter

barry houdini

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

dgd1212

ASKER

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))

Rgonzo1971

entered with CTRL+SHIFT+ENTER?

the formula should be afterwards between curly braces (do not try to type them)

dgd1212

ASKER

Rgonzo1971,
It finally dawned on me what/how to do that CTRL+SHIFT+ENTER. It Works!! Thank you!!