Hi! So after some Googling and forum-hunting, I've figured out how to count unique values in a column. Where I'm stuck, however, is trying to count unique values based on the first five characters in the cell. Here's what I have:
<TABLE style="WIDTH: 185pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=247><COLGROUP><COL style="WIDTH: 185pt; mso-width-source: userset; mso-width-alt: 9033" width=247><TBODY><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 185pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20 width=247>
<TABLE style="WIDTH: 185pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=247><COLGROUP><COL style="WIDTH: 185pt; mso-width-source: userset; mso-width-alt: 9033" width=247><TBODY><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 185pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=20 width=247>
Customer ID = Site ID (5) - Person ID (3)
</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20>
11111-001
</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20>
11111-002
</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20>
11111-003
</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20>
22222-001
</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20>
22222-002
</TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 185pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=247><COLGROUP><COL style="WIDTH: 185pt; mso-width-source: userset; mso-width-alt: 9033" width=247><TBODY><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 185pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=20 width=247>
# of Sites: 2
</TD></TR></TBODY></TABLE>
What I would like to do is count the number of sites by counting the unique site IDs in the set of customer IDs. I would prefer to do this in a single cell, if possible (in other words, I know how to do it if I create a whole separate column for the site number, but I'd rather not do that if I can avoid it). I'm guessing this might involve a frequency or sumproduct function? I have no clue.
Thank you!!