combining two formula

[paulsolar]

Hi All

I'm trying to make a formula dynamic but not having much success

What I'm trying to do is count how many unique entries there are in a column (text and numbers)

I'm using: ="Installers Using App " & SUM(IF(FREQUENCY(MATCH(G2:G194,G2:G194,0),MATCH(G2:G194,G2:G194,0))>0,1)) and this is working fine, but I need to make the G194 expand as the entries are added.

I've been trying to use =ROW(OFFSET(G1,COUNTA(G:G)-1,0)) in another cell to get it up update the first formula without any luck.

There must be an easier way of doing this but I'm struggling

Is there an easier way?

cheers

Paul

 

[jim may]

Create A Dynamic RangeName, like "MyRange" (Without the Quotes)

Menu, Formulas, Define Range,

enter name: MyRange

in the Refers to box (at the bottom) enter:

=OFFSET(Sheet1!$G$2,0,0,COUNTA(Sheet1!$G:$G),1) 'Substitute Your Sheet Name for Sheet1 used here...

Change your Formula accordingly to:

="Installers Using App " & SUM(IF(FREQUENCY(MATCH(MyRange,MyRange,0),MATCH(MyRange,MyRange,0))>0,1))

As your G:G range Increases or Decreases the Range "MyRange" will automatically change for you.

Good Luck,

Jim​

 

[paulsolar]

Hi Jim

Thanks a million, works like a charm

cheers

Paul