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

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,942
Reaction score
0
Points
36
Excel Version(s)
O365
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:

peter.abing

New member
Joined
Oct 24, 2012
Messages
34
Reaction score
0
Points
0
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.
 
Top