• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula help

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.
 

Attachments

  • Book2.xlsx
    8.9 KB · Views: 0
user advanced filter to select range and tick unique records to find out what you want.
upload_2015-8-4_16-57-55.png
 
Formula to find how many unique values in a given range
=SUM(1/COUNTIF(C3:C19,C3:C19))
enter as array formula with CSE
 
Other non-array solution:
SUMPRODUCT(1/COUNTIF(C3:C19,C3:C19))
SUM(INDEX(1/COUNTIF(C3:C19,C3:C19),,))
 
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,
 
all formulas applied in file. pls check
upload_2015-8-6_12-20-8.png
 

Attachments

  • count unique formulas.xlsx
    9.9 KB · Views: 0
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
 

Attachments

  • Copy of Book2.xlsx
    8.5 KB · Views: 0
  • Like
Reactions: 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!
 
Back
Top