Connt Unique items - with No C+S+E

[Joe Galvan]

Hi all...

I need to be able to count unique items and overlook the duplicates....

I have a formula that can do that, but it is a rather intesive formula, so it takes for ever when you have 2000+ lines trying to calculate.

the formula I am currently using is

{=SUM(IF(FREQUENCY(IF($A$5:$A$10000<>"", IF($D$5:$D$10000=P6,MATCH("~"&$A$5:$A$10000,$A$5:$A$10000&"",0))),ROW($A$5:$A$10000)-ROW($A$5)+1),1))}

closed with C+S+E

Any way this could be a lighter formula? or a differnt formula can do the same thing? I am leaning towards an Index/match formula but just not sure...

thanks,
JOE

 

[jb09]

If you are in '07 Excel, you can remove duplicates with the "remove duplicates" function in the "Data" tab, then run a count on the remaining items.

 

[T. Valko]

Hi all...

I need to be able to count unique items and overlook the duplicates....

I have a formula that can do that, but it is a rather intesive formula, so it takes for ever when you have 2000+ lines trying to calculate.

the formula I am currently using is

{=SUM(IF(FREQUENCY(IF($A$5:$A$10000<>"", IF($D$5:$D$10000=P6,MATCH("~"&$A$5:$A$10000,$A$5:$A$10000&"",0))),ROW($A$5:$A$10000)-ROW($A$5)+1),1))}

closed with C+S+E

Any way this could be a lighter formula? or a differnt formula can do the same thing? I am leaning towards an Index/match formula but just not sure...

thanks,
JOE

Are there any empty cells within the data range of column A?

What type of data is the unique count for? Text? Numbers? Both?

Can you post a few examples of the data in column A? Concatenating the tilde character and the empty string sure doesn't help if it's not needed.

 

[Joe Galvan]

yeah, but I cannot use the duplicate remover to this Data set... I am importing a table from an outside data source... so there are other formulas reading..

 

[Marcelo Branco]

Hi Joe,

Maybe using a helper column, say column AA, and entering this formula in AA5
=COUNTIF($A$5:A5,A5)=1
copy down till the end of your data

Then you can use this to count unique
=COUNTIF($AA$5:$AA$10000, TRUE)

HTH

M.

 

[Joe Galvan]

Col: A
Ord#
<TABLE style="WIDTH: 56pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=74><COLGROUP><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2706" width=74><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; WIDTH: 56pt; FONT-FAMILY: Arial; BACKGROUND: #dce6f1; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" height=17 width=74 align=right>10528683</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Arial; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=17 align=right>10556212</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: #dce6f1; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" height=17 align=right>10561557</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Arial; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=17 align=right>10566525</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: #dce6f1; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" height=17 align=right>10566525</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Arial; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=17 align=right>10581175</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: #dce6f1; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" height=17 align=right>10596942</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Arial; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=17 align=right>10602080</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: #dce6f1; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" height=17 align=right>10612449</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Arial; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=17 align=right>10618065</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: #dce6f1; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" height=17 align=right>10655893</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Arial; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=17 align=right>10659336</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: #dce6f1; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" height=17 align=right>10677144</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Arial; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=17 align=right>10703372</TD></TR></TBODY></TABLE>

Col: D
Color

<TABLE style="WIDTH: 52pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=69><COLGROUP><COL style="WIDTH: 52pt; mso-width-source: userset; mso-width-alt: 2523" width=69><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #d4d0c8; WIDTH: 52pt; FONT-FAMILY: Arial; BACKGROUND: #dce6f1; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" height=17 width=69>5950</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; FONT-FAMILY: Arial; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=17>AB01</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #d4d0c8; FONT-FAMILY: Arial; BACKGROUND: #dce6f1; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" height=17>5945</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; FONT-FAMILY: Arial; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=17>5945</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #d4d0c8; FONT-FAMILY: Arial; BACKGROUND: #dce6f1; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" height=17>5950</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; FONT-FAMILY: Arial; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=17>AB01</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #d4d0c8; FONT-FAMILY: Arial; BACKGROUND: #dce6f1; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" height=17>AB01</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; FONT-FAMILY: Arial; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=17>5947</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #d4d0c8; FONT-FAMILY: Arial; BACKGROUND: #dce6f1; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" height=17>AB01</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; FONT-FAMILY: Arial; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=17>5950</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #d4d0c8; FONT-FAMILY: Arial; BACKGROUND: #dce6f1; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" height=17>AB01</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; FONT-FAMILY: Arial; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=17>5950</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #d4d0c8; FONT-FAMILY: Arial; BACKGROUND: #dce6f1; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" height=17>AB01</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; FONT-FAMILY: Arial; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=17>5950</TD></TR></TBODY></TABLE>


there are no blanks...

the unique data would be the Ord #... there can be several line items to each ord#, so from the example above, the ord# 10566525 should only be counted once... based on the matching color sku.

 

[T. Valko]

Hi Joe,

Maybe using a helper column, say column AA, and entering this formula in AA5
=COUNTIF($A$5:A5,A5)=1
copy down till the end of your data

Then you can use this to count unique
=COUNTIF($AA$5:$AA$10000, TRUE)

HTH

M.

That may even be worse.

By the time you copy that formula down to row 10,000 this is how many cells you've calculated:

=SUMPRODUCT(ROW(1:9996))

 

[Joe Galvan]

Let me see if I can lay this out...

Columns A:D is where my table sits
A- ORD#
B-DATE
C-STYLE SKU
D- COLOR SKU


columns: N:Q holds the data to match
N-Style Sku
O-Product Name
P-Color Sku
Q - Color Name


What I am trying to count is the # of orders...


A
12345
12345
18976
18756
18756

C
AB01
AB01
5925
5926
5986


P
AB01

R - Count
result should say 1


Hope this lays it out clearly for you... Thanks...

 

[T. Valko]

Col: A
Ord#
<TABLE style="WIDTH: 56pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=74 border=0><COLGROUP><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2706" width=74><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #dce6f1; BORDER-LEFT: #95b3d7 0.5pt solid; WIDTH: 56pt; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Arial; HEIGHT: 12.75pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" align=right width=74 height=17>10528683</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BORDER-LEFT: #95b3d7 0.5pt solid; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Arial; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" align=right height=17>10556212</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #dce6f1; BORDER-LEFT: #95b3d7 0.5pt solid; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Arial; HEIGHT: 12.75pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" align=right height=17>10561557</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BORDER-LEFT: #95b3d7 0.5pt solid; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Arial; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" align=right height=17>10566525</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #dce6f1; BORDER-LEFT: #95b3d7 0.5pt solid; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Arial; HEIGHT: 12.75pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" align=right height=17>10566525</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BORDER-LEFT: #95b3d7 0.5pt solid; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Arial; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" align=right height=17>10581175</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #dce6f1; BORDER-LEFT: #95b3d7 0.5pt solid; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Arial; HEIGHT: 12.75pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" align=right height=17>10596942</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BORDER-LEFT: #95b3d7 0.5pt solid; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Arial; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" align=right height=17>10602080</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #dce6f1; BORDER-LEFT: #95b3d7 0.5pt solid; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Arial; HEIGHT: 12.75pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" align=right height=17>10612449</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BORDER-LEFT: #95b3d7 0.5pt solid; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Arial; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" align=right height=17>10618065</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #dce6f1; BORDER-LEFT: #95b3d7 0.5pt solid; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Arial; HEIGHT: 12.75pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" align=right height=17>10655893</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BORDER-LEFT: #95b3d7 0.5pt solid; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Arial; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" align=right height=17>10659336</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #dce6f1; BORDER-LEFT: #95b3d7 0.5pt solid; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Arial; HEIGHT: 12.75pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" align=right height=17>10677144</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BORDER-LEFT: #95b3d7 0.5pt solid; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Arial; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" align=right height=17>10703372</TD></TR></TBODY></TABLE>

Col: D
Color

<TABLE style="WIDTH: 52pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=69 border=0><COLGROUP><COL style="WIDTH: 52pt; mso-width-source: userset; mso-width-alt: 2523" width=69><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #dce6f1; BORDER-LEFT: #d4d0c8; WIDTH: 52pt; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Arial; HEIGHT: 12.75pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" width=69 height=17>5950</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Arial; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=17>AB01</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #dce6f1; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Arial; HEIGHT: 12.75pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" height=17>5945</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Arial; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=17>5945</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #dce6f1; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Arial; HEIGHT: 12.75pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" height=17>5950</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Arial; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=17>AB01</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #dce6f1; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Arial; HEIGHT: 12.75pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" height=17>AB01</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Arial; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=17>5947</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #dce6f1; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Arial; HEIGHT: 12.75pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" height=17>AB01</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Arial; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=17>5950</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #dce6f1; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Arial; HEIGHT: 12.75pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" height=17>AB01</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Arial; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=17>5950</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #dce6f1; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Arial; HEIGHT: 12.75pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" height=17>AB01</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Arial; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=17>5950</TD></TR></TBODY></TABLE>


there are no blanks...

the unique data would be the Ord #... there can be several line items to each ord#, so from the example above, the ord# 10566525 should only be counted once... based on the matching color sku.

OK, so you're counting unique NUMBERS.

Array entered**:

=SUM(IF(FREQUENCY(IF($D$5:$D$10000=P6,$A$5:$A$10000),$A$5:$A$10000),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.

That should improve the formula performance a bunch!

It's still going to be "relatively intensive" due to what it's doing plus the size of the range involved.

 

[jb09]

http://www.get-digital-help.com/2009/06/09/count-unique-values-in-a-column-in-excel/

Pretty sure it will still be intensive, but def need CTRL SHIFT ENTER