# Thread: Count of unique numbers starting from a specified number

1. ## Count of unique numbers starting from a specified number

Register for a FREE account, and/
or Log in to avoid these ads!

I want to count no of vendors as Local and Foreign in each PCS group.
Local vendors have Vendor# starting from 21 and foreign vendors have Vendor# starting from 22.
I have more than 150000 data lines in my data sheet to do this analysis.
I really need the help of excel gurus!
See attachment which has a part of my file.

2. Use

=SUMPRODUCT((\$A\$2:\$A\$1000=\$K5)*(LEFT(\$C\$2:\$C\$1000,2)="21")/COUNTIF(\$C\$2:\$C\$1000,\$C\$2:\$C\$1000&""))

and

=SUMPRODUCT((\$A\$2:\$A\$1000=\$K5)*(LEFT(\$C\$2:\$C\$1000,2)="22")/COUNTIF(\$C\$2:\$C\$1000,\$C\$2:\$C\$1000&""))

3. sampleX.xlsx
Please see attached.
This should work.

I'm not an expert and I think there is a better way to do this.

What I've done,:
1. I added a helper column. Column I will tell if it is the first time that the vendor ID appeared (result is 1). If it has already appeared in another row above, it will result as 0.
2. Used the formula =SUMPRODUCT(IF(LEFT(\$C:\$C,2)="21",1,0),IF(\$A:\$A=\$K5,1,0),\$I:\$I) in cell L5. Instead of just pressing Enter, you press Ctrl+Shft+Enter.

I hope this helps.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•