Counting Unique Values With Frequency/Len/Match

[ScottPotty]

Hello,

For two days now I have been trying to figure out how to sum the total of unique names in a column.

I found from your site, {=sum(1/countif(array,array))} however that doesn't work.

Then I found http://office.microsoft.com/en-us/e...e number of unique values by using functions

There are three great solutions here, but the third doesn't work for me. Regrettably, that's the one I need, because I'm interested in automating this performance, and there will be blank cells.

The formula I'm trying to use is:

=SUM(IF(FREQUENCY(IF(LEN(A2:A10)>0,MATCH(A2:A10,A2:A10,0),""), IF(LEN(A2:A10)>0,MATCH(A2:A10,A2:A10,0),""))>0,1))

I get a #Value! error, and I'm pretty sure it's because of the LEN command. I finding that it doesn't work in an array, but rather it works for single cells.

Do you have any recommendations?

I do know about the Advanced Filter approach, but I'm not sure how to automate.

I want to accomplish two things really:
1. Find Unique Occurrences
2. Add the total number of Unique Occurrences.

Thanks,

ScottPotty

 

[ZVI]

Hi ScottPotty!

Refer to example 6 from this link:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

=SUMPRODUCT((A1:A20<>"")/(COUNTIF(A1:A20,A1:A20)+(A1:A20="")))

Regards,
Vladimir

 

[Yogi Anand]

Hi Scott:

Welcome to MrExcel Board!

Let us have a look at the following ...

Book1
	A	B	C	D	E
1
2	John	4	2	2	John
3	Mary		2	3	Mary
4					Kay
5					Ron
6	Kay		1	6
7	John		0
8	Mary		0
9
10
11	Ron		1	11
12
Sheet7 (2)

It may be possible to simplify these formulas ... but is this the sort of thing you are looking for?

 

[ScottPotty]

Hey guys,

Thanks for the assistance. This is awesome, and thanks for the welcome. I'm glad to be here.

Vladimir (ZVI), your recommendation works great!

Yogi, that example is amazing! B2 is exactly what I need. I'm trying to create an average by plugging in two different formulas. I'm using simple arithmetic, but the formula is a headache.

I need avg=(x)/y where the top of the equation is a sum of all products sold divided by the sum of unique rep names.

I'm not sure if I could figure out how to plug in your array from B2 into my formula.

Currently, thanks to ZVI, I have this long formula, which I'm okay with, but do you think it'll slow the worksheet down?

The formula is:

=SUMPRODUCT(--('Products Sold by Rep Report'!$J:$J="ProStores Express")/((SUMPRODUCT(('Products Sold by Rep Report'!$A$2:$A$1000<>"")/(COUNTIF('Products Sold by Rep Report'!$A$2:$A$1000,'Products Sold by Rep Report'!$A$2:$A$1000)+('Products Sold by Rep Report'!$A$2:$A$1000=""))))))

 

[ScottPotty]

You know, now that I look at the formula, it's not the formula that is long, it's the name of the worksheet that is long. Nonetheless, if you have any tips, I'm more than happy to receive them.

 

[ZVI]

For speeding up it is recommended to avoid calculating of full column cells – shrink references to used range. Use $J1:$J1000 instead of $J:$J reference.
Dynamic range is suitable for automatic referencing to used column cells.

Calculation time can be dramaticly decreased in hundred times by the aid of VBA user defined function with usage of collection for unique value counting. I prefer this way.

But another smart way is:
1. Sort A:A column
2. Add for example in B2 the formula: =IF(AND(A2<>"",A2<>A1),1,0) and copy (or fill) it down up to B1000.
3. In empty cell calculate unique count: =SUM(B1:B1000)

 

[Aladin Akyurek]

You know, now that I look at the formula, it's not the formula that is long, it's the name of the worksheet that is long. Nonetheless, if you have any tips, I'm more than happy to receive them.

Given that:

http://www.mrexcel.com/forum/showthread.php?t=292473

Control+shift+enter, not just enter...

=SUM(IF(FREQUENCY(IF(Id!$A$2:$A$1000<>"",IF(Id!$J$2:$A$J000="ProStores Express",MATCH("~"&Id!$A$2:$A$1000,Id!$A$2:$A$1000&"",0))),ROW(Id!$A$2:$A$1000)-ROW(Id!$A$2)+1),1))

Substitute the name of your worksheet for Id.