• 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.

How to Calculate.

P

premjeet

New Member
I have a data as showing below chart.

[pre]
Code: 
A	B	C	D	E	Count of Operators.(This is Answer)
WCDMA	CDMA	GSM	GSM	CDMA	3
CDMA	CDMA	GSM	GSM	CDMA	2
GSM	CDMA	GSM	GSM	CDMA	2
CDMA	CDMA	CDMA	CDMA	CDMA	1
WCDMA	WCDMA	WCDMA	WCDMA	WCDMA	1
[/pre]
I need a formula that can show how many operators are in columns as showing answer.
 
Hi Premjeet ,


Can you go through this link , and apply it to your situation ?


http://www.myonlinetraininghub.com/excel-factor-9-count-unique-items-in-a-list


Another link is :


http://chandoo.org/wp/2012/07/26/formula-forensics-025/


Narayan
 
Thanks Narayank,


I have used the forensics formula.


But what if one cell is blank. It showing error #DIV/0! message.


WCDMA CDMA GSM GSM 3

GSM GSM GSM CDMA 2

GSM CDMA GSM #DIV/0!
 
Using your sample from the 1st post, put this formula in F1 and fill down:

[pre]
Code: 
=SUM(--(FREQUENCY(IF(A1:E1<>"",MATCH(A1:E1,A1:E1,0)),COLUMN(A1:E1)-COLUMN(A1)+1)>0))
[/pre]
This is an array formula so when you type it into the formula bar you must complete the entry with CTRL+SHIFT+ENTER and Excel should automatically surround it with {}.


If you're interested in an explanation of this formula then I adapted from section 2.2 here:

http://colinlegg.wordpress.com/2013/01/10/count-distinct-unique-and-successive-values-using-frequency/
 
Ah okay - I was using your data from post 1. If it goes in E1 then you'll need to adjust the ranges to avoid circular references:

[pre]
Code: 
=SUM(--(FREQUENCY(IF(A1:D1<>"",MATCH(A1:D1,A1:D1,0)),COLUMN(A1:D1)-COLUMN(A1)+1)>0))
[/pre]
 
You must log in or register to reply here.
Back
Top