Michael,
I read this that you want to include anything starting with P1 in the P1
numbers, so a slight change to Ron's suggestion
=SUM(IF(FREQUENCY(IF((A1:A10<"")*(LEFT(B1:B10,2)= "P1"),MATCH(A1:A10,A1:A10,
0)),ROW(INDIRECT("1:"&ROWS(A1:A10))))0,1))
still an array formula.
--
HTH
Bob Phillips
"Ron Rosenfeld" wrote in message
...
On Tue, 28 Jun 2005 07:39:10 -0700, "Michael"
wrote:
If my data contains like "P1-C40XXX", and i want to include it in the
counting. How to achieve it?
Try the array formula:
=SUM(IF(FREQUENCY(IF((A1:A10<"")*((B1:B10="P1")+
(LEFT(B1:B10,6)="P1-C40")),MATCH(A1:A10,A1:A10,0)),
ROW(INDIRECT("1:"&ROWS(A1:A10))))0,1))
--ron
|