Function/Formula in Excel to count Unique values matching criteria

[mando415]

Hi - I have a worksheet. I would like to count unique number of "Trans" in column A only if value of cells in column B "Type" equals "Return". In example below, I would want to see the value "3" as total.

I am banging my head over this and would appreciate any advise.

Trans Type
1 Return
2 Return
2 Return
3 Exch
4 Exch
5 Return
5 Return

Thank you

 

[Aladin Akyurek]

Worked perfect! Thanks...

You are welcome.

wish their was a simplier was to get unique counts thought.

A specialized function like Longre's COUNTDIFF, for example.

 

[Superstar31]

You are welcome.



A specialized function like Longre's COUNTDIFF, for example.

Where can I find Longre's COUNTDIFF?

 

[Superstar31]

Where can I find Longre's COUNTDIFF?

For 64bit!

 

[Aladin Akyurek]

For 64bit!

Alas, it's only available for the 32bit windows systems as part of the morefunc.xll add-in.

 

[pitFlyz]

I have been trying all day to develop a formula that will return all the unique numbers in an array with the condition that they are less than 96 and greater than 0. I greatly appreciate any help here. Thank you so much.

 

[Aladin Akyurek]

I have been trying all day to develop a formula that will return all the unique numbers in an array with the condition that they are less than 96 and greater than 0. I greatly appreciate any help here. Thank you so much.

Row\Col	A​	B​	C​
1​	Numbers		9​
2​	77​		Unique numbers
3​	48​		4​
4​	48​		19​
5​	36​		36​
6​	4​		48​
7​	4​		52​
8​	36​		55​
9​	19​		59​
10​	19​		60​
11​	59​		77​
12​	59​
13​	36​
14​	55​
15​	55​
16​	52​
17​	0​
18​	0​
19​	-5​
20​	60​
21​	60​

C1, control+shift+enter (cse), not just enter:

=SUM(IF(FREQUENCY(IF(A2:A21>0,IF(A2:A21<96,A2:A21)),A2:A21),1))

C3, cse and copy down:

=IF(ROWS($C$3:C3) <= $C$1,MIN(IF($A$2:$A$21 > 0,IF($A$2:$A$21 < 96,
    IF(ISNUMBER(MATCH($A$2:$A$21,$C$2:C2,0)),"",$A$2:$A$21)))),"")

 

[GotYaNumba]

Hello everyone. I'm having some trouble with this. My data looks like:

unique email | categorization

jobob@excel | 1
jobob@excel | 1
jobob@excel | 0
andyj@excel | 1
doniy@excel | 0

I want to count the number of unique emails that have a classification of 1. So in this case, the answer would be "2", for both jobob@excel and andyj@excel. I've tried the array formula at the beginning of this thread, but I get an answer of 0.

{=SUM(IF(FREQUENCY(IF(B2:B6=1, A2:A6), IF(B2:B6=1, A2:A6))>0,1))}

I have a feeling that FREQUENCY is not working as expected. {=FREQUENCY(A2, A2:A6)} returns 0, when I'd expect it to return 3.

Thanks for any help.

 

[Domenic]

Since you're counting unique text values instead of numerical values...

=SUM(IF(FREQUENCY(IF(B2:B6=1,IF(A2:A6<>"",MATCH("~"&A2:A6,A2:A6&"",0))),ROW(A2:A6)-ROW(A2)+1)>0,1))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[GotYaNumba]

Thanks a bunch! It does. Now if I could only understand it . Leave that to me. Cheers.

 

[Domenic]

You're very welcome!

Cheers!