Count of unique entries with multiple criteria

[GDunn]

Hi,

I have a large list of data where I would like to count the unique company names (column B), where Date (columnA) = 08/01/2011.
Would also like to be able to add another condition once this is working. (where month ="Jan").

<TABLE style="WIDTH: 116pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=155><COLGROUP><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width=75><COL style="WIDTH: 60pt; mso-width-source: userset; mso-width-alt: 2925" width=80><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #95b3d7; WIDTH: 56pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl65 height=20 width=75>Date</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #95b3d7; WIDTH: 60pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" id=td_post_1533340 class=xl65 width=80>Company</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #7f7f7f 0.5pt solid; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl66 height=20 align=right>08/01/2011</TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #7f7f7f 0.5pt solid; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl68>test1</TD></TR>Result in this example = 6

<TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl67 height=20 align=right>08/01/2011</TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl69>test2</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl67 height=20 align=right>08/01/2011</TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl69>test3</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl67 height=20 align=right>08/01/2011</TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl69>test4</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl67 height=20 align=right>08/01/2011</TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl69>test5</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl67 height=20 align=right>08/01/2011</TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl69>test6</TD></TR></TBODY></TABLE>

 

[Aladin Akyurek]

E2: 08/01/2011

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

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

H2: 1-Jan-11

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

=SUM(IF(FREQUENCY(IF($B$2:$B$7<>"",
    IF(ISNUMBER($A$2:$A$7),TEXT($A$2:$A$7,"mmmyy")=TEXT(H2,"mmmyy"),
      MATCH("~"&$B$2:$B$7,$B$2:$B$7&"",0)))),
        ROW($B$2:$B$7)-ROW($B$2)+1),1))

If there are no chars like < which surround the company entries,
you can safely remove the "~"& and &"" bits from the above formulas.

 

[GDunn]

Perfect. Thanks

 

[Aladin Akyurek]

Perfect. Thanks

You are welcome. Thanks for providing feedback.