sumproduct
 

col a col b answer
3sha000011 0124 3sha & 0 =1
3sha000012 2336 3sha & 2 =1
4sha000013 0495 4sha & 0 =2
4sha000002 0113


if col a begins with 3sha and col b begins with 0 count
answer 1

if col a begins with 4sha and col b begins with 0 count
answer 2

I tried countif(and(left(a2:a5,4)="3sha",b2:b5,1)="0")) and couldn't get it to work.

i have 8000+ in col a and 8000+ in col b

sumproduct
 

Hi,

Am Sat, 11 Aug 2012 03:38:23 +0000 schrieb old coach:

col a col b answer
3sha000011 0124 3sha & 0 =1
3sha000012 2336 3sha & 2 =1
4sha000013 0495 4sha & 0 =2
4sha000002 0113

if col a begins with 4sha and col b begins with 0 count
answer 2

if you use xl 2007 0r 2010 then:
=COUNTIFS(A2:A5,"4sha*",B2:B5,"0*")
for earlier versions:
=SUMPRODUCT(--(LEFT(A2:A5,4)="4sha"),--(LEFT(B2:B5,1)="0"))


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Great! It worked. Let's go one step futher.

add one 2 more conditions, same as B2:B5.
if cell has "J 0" as the lead characters or "Y 0"
It will work separately but when i add it to the above formula as a 3rd & 4th condition, it shows 0 as answer.

sumproduct
 

Hi,

Am Sat, 11 Aug 2012 17:39:13 +0000 schrieb old coach:

add one 2 more conditions, same as B2:B5.
if cell has "J 0" as the lead characters or "Y 0"
It will work separately but when i add it to the above formula as a 3rd
& 4th condition, it shows 0 as answer.

I hope, i understood your problem correctly.
Try:
=SUMPRODUCT((LEFT(A2:A5,4)="4sha")*((LEFT(B2:B5,3) ="Y
0")+(LEFT(B2:B5,3)="J 0")+(LEFT(B2:B5,1)="0")))


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2