Formula help

[MohanBabu1023]

Hi,
I need a help regarding formula,the formula I need in D3 cell as I typed manually in D3 cell.Please find the attached file.

 

[Asheesh]

Hi,

if i understand you right..use the below in D3 and drag it down

=RIGHT(C3,1)+0

 

[Asheesh]

Isnt the solution in #2 working.. ?? If so, can you explain your exact requirement..

 

[MohanBabu1023]

thank for attending,the number may any thing,please find the attached file.

 

[MohanBabu1023]

Asheesh have understand the question.

 

[Aby]

do you want to find out how many unique entries are there?

 

[MohanBabu1023]

yes

 

[Aby]

user advanced filter to select range and tick unique records to find out what you want.

 

[MohanBabu1023]

Sorry G,

i need only formula.

 

[Aby]

Formula to find how many unique values in a given range
=SUM(1/COUNTIF(C3:C19,C3:C19))
enter as array formula with CSE

 

[Asheesh]

Other non-array solution:
SUMPRODUCT(1/COUNTIF(C3:C19,C3:C19))
SUM(INDEX(1/COUNTIF(C3:C19,C3:C19),,))

 

[MohanBabu1023]

Hi Aby & Asheesh,

It's not working, can u apply the formula in the attached sample file.

 

[Somendra Misra]

Hi,

Try below formula in D3 and copy down:

=IF(COUNTIF(C$3:C3,C3)=1,MAX(D$2:D2)+1,OFFSET($D$2,MATCH(C3,C$2:C2,0)-1,0,1,1))

Regards,

 

[Aby]

why not working? what error you get? my formula is array so enter it with Ctrl+shft+enter

 

[Aby]

all formulas applied in file. pls check

 

[NARAYANK991]

Hi ,

Enter the following , as an array formula , using CTRL SHIFT ENTER , in D3 and copy down.

=SUM(1/COUNTIF($C$3:C3,$C$3:C3))

See the attached file. The above formula will work only if your data in column C is grouped together , so that all identical entries occur together.

Narayan

 

[Aby]

Formula to find how many unique values in a given range
=SUM(1/COUNTIF(C3:C19,C3:C19))
enter as array formula with CSE

Hi ,

Enter the following , as an array formula , using CTRL SHIFT ENTER , in D3 and copy down.

=SUM(1/COUNTIF($C$3:C3,$C$3:C3))

See the attached file. The above formula will work only if your data in column C is grouped together , so that all identical entries occur together.

Narayan

A simple change in the range selection does all the trick. great one Ninja G, good learning stuff!