Count Unique and specific values

[jammerdk]

Hi Guys

Having a large spreadsheet with Columns A to F filled with data.

I'm trying to create a formula that's count the unique value "Order no." (Coloumn F) but only the ones between certain Departments Column(H) a certain "Error type" in Column(J).

Hope you can point me in the right direction.

 

[Yahya]

try this Idea
<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 /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Order Number</td><td style="text-align: right;;"></td><td style=";">DepartMent</td><td style="text-align: right;;"></td><td style=";">Error type</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">111</td><td style="text-align: right;;"></td><td style=";">Dep 1</td><td style="text-align: right;;"></td><td style=";">Type 1</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">111</td><td style="text-align: right;;"></td><td style=";">Dep 2</td><td style="text-align: right;;"></td><td style=";">Type 1</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">111</td><td style="text-align: right;;"></td><td style=";">Dep 3</td><td style="text-align: right;;"></td><td style=";">Type 2</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">111</td><td style="text-align: right;;"></td><td style=";">Dep 1</td><td style="text-align: right;;"></td><td style=";">Type 1</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">112</td><td style="text-align: right;;"></td><td style=";">Dep 2</td><td style="text-align: right;;"></td><td style=";">Type 1</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">112</td><td style="text-align: right;;"></td><td style=";">Dep 3</td><td style="text-align: right;;"></td><td style=";">Type 1</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">112</td><td style="text-align: right;;"></td><td style=";">Dep 1</td><td style="text-align: right;;"></td><td style=";">Type 2</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">112</td><td style="text-align: right;;"></td><td style=";">Dep 2</td><td style="text-align: right;;"></td><td style=";">Type 3</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">112</td><td style="text-align: right;;"></td><td style=";">Dep 3</td><td style="text-align: right;;"></td><td style=";">Type 1</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">113</td><td style="text-align: right;;"></td><td style=";">Dep 1</td><td style="text-align: right;;"></td><td style=";">Type 1</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">113</td><td style="text-align: right;;"></td><td style=";">Dep 2</td><td style="text-align: right;;"></td><td style=";">Type 2</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">113</td><td style="text-align: right;;"></td><td style=";">Dep 3</td><td style="text-align: right;;"></td><td style=";">Type 3</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;">113</td><td style="text-align: right;;"></td><td style=";">Dep 1</td><td style="text-align: right;;"></td><td style=";">Type 1</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;">113</td><td style="text-align: right;;"></td><td style=";">Dep 2</td><td style="text-align: right;;"></td><td style=";">Type 1</td></tr><tr ><td style="color: #161120;text-align: center;">16</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><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</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><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</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><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;;">111</td><td style="text-align: right;;">3</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">Sheet1</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">G19</th><td style="text-align:left">{=SUM(<font color="Blue">IF(<font color="Red">FREQUENCY(<font color="Green">IF(<font color="Purple">F2:F15<>"",IF(<font color="Teal">F2:F15=F19,MATCH(<font color="#FF00FF">H2:H15&"#"&J2:J15,H2:H15&"#"&J2:J15,0</font>)</font>)</font>),ROW(<font color="Purple">F2:F15</font>)-ROW(<font color="Purple">F2</font>)+1</font>),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 />

 

[Aladin Akyurek]

Hi Guys

Having a large spreadsheet with Columns A to F filled with data.

I'm trying to create a formula that's count the unique value "Order no." (Coloumn F) but only the ones between certain Departments Column(H) a certain "Error type" in Column(J).

Hope you can point me in the right direction.

Assuming that you want to count distinct orders corresponding to a department associated with a certain error type...

Let K2 house a department of interest and L2 an error type of interest.

M2, control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF($F$2:$F$100<>"",
    IF($H$2:$H$100=K2,IF($J2:$J$100=L2,
     MATCH("~"&$F$2:$F$100,$F$2:$F$100&"",0)))),
       ROW($F$2:$F$100)-ROW($F$2)+1),1))

Note. If the entries in F are not surrounded with special meaning chars like <, we can safely remove the "~"& and &"" bits from the foregoing formula.

 

[jammerdk]

Yup that did it Aladin

thanx a million

 

[Aladin Akyurek]

Yup that did it Aladin

thanx a million

You are welcome. Thanks for providing feedback.

 

[jammerdk]

Hi Aladin

If I'd like to add between certain Years to the formula

=SUM(IF(FREQUENCY(IF($F$2:$F$100<>"",
IF($H$2:$H$100=K2,IF($J2:$J$100=L2,
MATCH("~"&$F$2:$F$100,$F$2:$F$100&"",0)))),
ROW($F$2:$F$100)-ROW($F$2)+1),1))

and ideas

 

[Aladin Akyurek]

Hi Aladin

If I'd like to add between certain Years to the formula

=SUM(IF(FREQUENCY(IF($F$2:$F$100<>"",
IF($H$2:$H$100=K2,IF($J2:$J$100=L2,
MATCH("~"&$F$2:$F$100,$F$2:$F$100&"",0)))),
ROW($F$2:$F$100)-ROW($F$2)+1),1))

and ideas

Let $G$2:$G$100 house the date entries, M2:N2 the year criteria with M2 < N2...

O2, control+shift+entee, not just enter:

=SUM(IF(FREQUENCY(IF($F$2:$F$100<>"",
IF(YEAR($G$2:$G$100)>=M2,IF(YEAR($G$2:$G$100)<=N2,
IF($H$2:$H$100=K2,IF($J2:$J$100=L2,
MATCH("~"&$F$2:$F$100,$F$2:$F$100&"",0)))))),
ROW($F$2:$F$100)-ROW($F$2)+1),1))

If you are not on an Excel version of 2007 or later...

=SUM(IF(FREQUENCY(IF($F$2:$F$100<>"",
IF((YEAR($G$2:$G$100)>=M2)*(YEAR($G$2:$G$100)<=N2),
IF(($H$2:$H$100=K2)*($J2:$J$100=L2),
MATCH("~"&$F$2:$F$100,$F$2:$F$100&"",0)))),
ROW($F$2:$F$100)-ROW($F$2)+1),1))

 

[jammerdk]

works like a charm ...thanks

 

[Aladin Akyurek]

works like a charm ...thanks

You are welcome. Thanks for providing feedback.