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.

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][/tr]
[tr][td]

1​

[/td][td]Numbers[/td][td][/td][td]

9​

[/td][/tr]

[tr][td]

2​

[/td][td]

77​

[/td][td][/td][td]Unique numbers[/td][/tr]

[tr][td]

3​

[/td][td]

48​

[/td][td][/td][td]

4​

[/td][/tr]

[tr][td]

4​

[/td][td]

48​

[/td][td][/td][td]

19​

[/td][/tr]

[tr][td]

5​

[/td][td]

36​

[/td][td][/td][td]

36​

[/td][/tr]

[tr][td]

6​

[/td][td]

4​

[/td][td][/td][td]

48​

[/td][/tr]

[tr][td]

7​

[/td][td]

4​

[/td][td][/td][td]

52​

[/td][/tr]

[tr][td]

8​

[/td][td]

36​

[/td][td][/td][td]

55​

[/td][/tr]

[tr][td]

9​

[/td][td]

19​

[/td][td][/td][td]

59​

[/td][/tr]

[tr][td]

10​

[/td][td]

19​

[/td][td][/td][td]

60​

[/td][/tr]

[tr][td]

11​

[/td][td]

59​

[/td][td][/td][td]

77​

[/td][/tr]

[tr][td]

12​

[/td][td]

59​

[/td][td][/td][td][/td][/tr]

[tr][td]

13​

[/td][td]

36​

[/td][td][/td][td][/td][/tr]

[tr][td]

14​

[/td][td]

55​

[/td][td][/td][td][/td][/tr]

[tr][td]

15​

[/td][td]

55​

[/td][td][/td][td][/td][/tr]

[tr][td]

16​

[/td][td]

52​

[/td][td][/td][td][/td][/tr]

[tr][td]

17​

[/td][td]

0​

[/td][td][/td][td][/td][/tr]

[tr][td]

18​

[/td][td]

0​

[/td][td][/td][td][/td][/tr]

[tr][td]

19​

[/td][td]

-5​

[/td][td][/td][td][/td][/tr]

[tr][td]

20​

[/td][td]

60​

[/td][td][/td][td][/td][/tr]

[tr][td]

21​

[/td][td]

60​

[/td][td][/td][td][/td][/tr]
[/table]

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!