# Count Unique Numbers (no blanks) + Only Counting Those Numbers That Start with 3

So I have a couple of columns where I am trying to get the Unique count of the order number.  Depending on where they purchased the item(s) the first digit of the order number is different.  So I did a formula to get the overall count of the distinct order numbers

``````=SUM(IF(FREQUENCY(MATCH(Sales_Invoice_Line[Sales Invoice Header - Order Number],Sales_Invoice_Line[Sales Invoice Header - Order Number],0),MATCH(Sales_Invoice_Line[Sales Invoice Header - Order Number],Sales_Invoice_Line[Sales Invoice Header - Order Number],0))>0,1))
``````

Now I wanted to break down Number of Orders per location, which if the Order number starts with 3 then its one location if it starts with 4 its another location, etc.  I did SUMPRODUCT for the total \$\$ and that formula looks like

``````=SUMPRODUCT(--(LEFT(Sales_Invoice_Line[Sales Invoice Header - Order Number],1)="3"),--(Sales_Invoice_Line[Type]="Item"),Sales_Invoice_Line[Amount])
``````

But not sure how to combine the 2 to count uniques -- my attempt

``````=SUM(IF(FREQUENCY(MATCH((LEFT(Sales_Invoice_Line[Sales Invoice Header - Order Number],1)="3"),(LEFT(Sales_Invoice_Line[Sales Invoice Header - Order Number],1)="3"),0),MATCH((LEFT(Sales_Invoice_Line[Sales Invoice Header - Order Number],1)="3"),(LEFT(Sales_Invoice_Line[Sales Invoice Header - Order Number],1)="3"),0))>0,1))
``````

I know, I know .. crappy

Any thoughts?
###### Who is Participating?

Commented:
To count the number of different order numbers beginning with 3 try this formula

see attached where I used the above formula but with a simple range A2:A10

regards, barry
uniques.xlsx
0

Commented:
to find ourselves with these name ranges could you post a workbook that contains this info ?
gowflow
0

Author Commented:
It works!  Sweet!  Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.