How do I Count Unique Values With Conditions?

[Scrambler307]

I need to count unique values using conditions. I believe I need an aray formula. Can anyone help me? Here is my data. It has blanks and text in the columns. I need to count "Job type" by unique "Job Number".
The answer I am looking for should look like this.

ANSWER
A = 3
P = 4
J = 1
V = 1
E = 1

RAW DATA
A B
Job_Type Job_Number
A 2011-0161
A 2011-0107
P 1991-341A
P 1991-341A

P 1991-341A

J 2011-588A

P 2007-0001

P 2011-0118
A 2011-0157
A 2011-0157
P 1991-341A
P 2011-0008

P 2011-0118
E 2011-0001
V 2011-0001

 

[Firefly2012]

Hi

Here's one way:
<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">Job_Type</td><td style="font-weight: bold;;">Job_Number</td><td style="font-weight: bold;text-align: right;;"></td><td style="font-weight: bold;text-align: right;;"></td><td style="font-weight: bold;;">Unique Job types</td><td style="font-weight: bold;text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">A</td><td style=";">2011-0161</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">A</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">A</td><td style=";">2011-0107</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">P</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">P</td><td style=";">1991-341A</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">J</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">P</td><td style=";">1991-341A</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">V</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">P</td><td style=";">1991-341A</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">E</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">J</td><td style=";">2011-588A</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">P</td><td style=";">2007-0001</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">P</td><td style=";">2011-0118</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">A</td><td style=";">2011-0157</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">A</td><td style=";">2011-0157</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style=";">P</td><td style=";">1991-341A</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style=";">P</td><td style=";">2011-0008</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style=";">P</td><td style=";">2011-0118</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style=";">E</td><td style=";">2011-0001</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style=";">V</td><td style=";">2011-0001</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F2</th><td style="text-align:left">{=SUM(<font color="Blue">IF(<font color="Red">FREQUENCY(<font color="Green">IF(<font color="Purple">$A$2:$A$16=E2,MATCH(<font color="Teal">$B$2:$B$16,$B$2:$B$16,0</font>)</font>),MATCH(<font color="Purple">$B$2:$B$16,$B$2:$B$16,0</font>)</font>)>0,1</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

 

[Scrambler307]

The Formula you gave me does not handle blank values. Can this be done?

 

[Firefly2012]

If you amend it to the following it should work:

{=SUM(IF(FREQUENCY(IF($A$2:$A$16=E2,MATCH($B$2:$B$16&"",$B$2:$B$16&"",0)),MATCH($B$2:$B$16&"",$B$2:$B$16&"",0))>0,1))}

amendments are in red

 

[Aladin Akyurek]

I need to count unique values using conditions. I believe I need an aray formula. Can anyone help me? Here is my data. It has blanks and text in the columns. I need to count "Job type" by unique "Job Number".
The answer I am looking for should look like this.

ANSWER
A = 3
P = 4
J = 1
V = 1
E = 1

RAW DATA
A B
Job_Type Job_Number
A 2011-0161
A 2011-0107
P 1991-341A
P 1991-341A

P 1991-341A

J 2011-588A

P 2007-0001

P 2011-0118
A 2011-0157
A 2011-0157
P 1991-341A
P 2011-0008

P 2011-0118
E 2011-0001
V 2011-0001

With...

E2: A
E3: P
etc.

Control+shift+enter, not just enter, and copy down:

=SUM(IF(FREQUENCY(IF($B$2:$B$100<>"",
  IF($A$2:$A$100=E2,MATCH("~"&$B$2:$B$100,$B$2:$B$100&"",0))),
   ROW($B$2:$B$100)-ROW($B$2)+1),1))

If there are no special meaning chars like < around the target entries, the "~"& and &"" bits can be removed from the formula.

 

[T. Valko]

I need to count unique values using conditions. I believe I need an aray formula. Can anyone help me? Here is my data. It has blanks and text in the columns. I need to count "Job type" by unique "Job Number".
The answer I am looking for should look like this.

ANSWER
A = 3
P = 4
J = 1
V = 1
E = 1

RAW DATA
A B
Job_Type Job_Number
A 2011-0161
A 2011-0107
P 1991-341A
P 1991-341A

P 1991-341A

J 2011-588A

P 2007-0001

P 2011-0118
A 2011-0157
A 2011-0157
P 1991-341A
P 2011-0008

P 2011-0118
E 2011-0001
V 2011-0001

Try this...

Book1
	A	B	C	D	E
2	A	2011-0161	_	A	3
3	A	2011-0107	_	P	4
4	P	1991-341A	_	J	1
5	P	1991-341A	_	V	1
6	_	_	_	E	1
7	P	1991-341A	_	_	_
8	_	_	_	_	_
9	J	2011-588A	_	_	_
10	_	_	_	_	_
11	P	2007-0001	_	_	_
12	_	_	_	_	_
13	P	2011-0118	_	_	_
14	A	2011-0157	_	_	_
15	A	2011-0157	_	_	_
16	P	1991-341A	_	_	_
17	P	2011-0008	_	_	_
18	_	_	_	_	_
19	P	2011-0118	_	_	_
20	E	2011-0001	_	_	_
21	V	2011-0001	_	_	_
22	_	_	_	_	_
23	_	_	_	_	_
24	_	_	_	_	_
25	_	_	_	_	_
Sheet4

This array formula** entered in E2 and copied down:

=SUM(IF(FREQUENCY(IF(B$2:B$25<>"",IF(A$2:A$25=D2,MATCH(B$2:B$25,B$2:B$25,0))),ROW(B$2:B$25)-ROW(B$2)+1),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[Firefly2012]

Aladin and Biff - thanks. Both your solutions are much better than mine because they avoid the repeated call to MATCH. I'll bear than in mind for the future

 

[Aladin Akyurek]

[...]avoid[ing] the repeated call to MATCH. I'll bear than in mind for the future

Here is a relevant link:

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

 

[Scrambler307]

Thanks all. I will give it a try this afternoon.

 

[Scrambler307]

I re-wrote some of th formula to make it work across several worksheets within the workbook and I had no problems. I processed a ton of data, Thanks. Lastly, and I dont know if this is possible, can anyone explain the science behind the aray formula so I can write my own? Is there a trick to writing these?
Thanks a bunch!