I am using
sum(countif(sheet2!$c$2:$c$1000,match(b14:b23,shee t2!$a$1:$a$1000,0)&"/?/*/*")) this is array formula
sumproduct(--(countif(sheet2!$c$2:$c$1000,match(b14:b23,sheet2! $a$1:$a$1000)&"/?/*/*")))
-both the formulas working fine with result of 27 according to data
available in sheet2.
I want to add another countif criteria to the above formulas, that is in
sheet2!a2:a1000 range,that means formula should consider both the
conditions,according to my calculation I should get less of above
results(<27).I would like add criteria
left(sheet2!$a$1:$a$1000,7)="DAVID" as this sheet2!a1:a1000 contains names.
Is it possible to modify the above formula ?.I have tried alot but could not
succeed.