Count of unique numbers starting from a specified number

dilini.gunathilake

New member
Joined
Feb 11, 2013
Messages
1
Reaction score
0
Points
0
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.
 

Attachments

  • sampleX.xlsx
    12.6 KB · Views: 12
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&""))
 
Last edited:
View attachment 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.
 
Back
Top