The fact there is data in A3,4,5, and they are not always even is irrelevant
to my formula. It works on all of the examples you have posted to date
(assuming I understand your requirement correctly), never returning 0.

It might just be wrap-around in the NG posting, so try this amended version
of the second formula

=SUMPRODUCT(--ISNUMBER(FIND(MID(B2,
FIND("~",SUBSTITUTE(","&B2&",",",","~",ROW(INDIREC T("1:"&C2)))),
FIND("~",SUBSTITUTE(","&B2&",",",","~",ROW(INDIREC T("2:"&C2+1))))-
FIND("~",SUBSTITUTE(","&B2&",",",","~",ROW(INDIREC T("1:"&C2))))-1),A2)))

and copy down

--

HTH

RP
(remove nothere from the email address if mailing direct)


"sun1x" wrote in
message ...

Bob Phillips Wrote:
Have you tried my formula?



Yes, Bob.

The answer is always 0 when I put your formulas in C2 and D2, and drag
down to fill up the whole sheet. I guess because there are data in
A3,4,5... and B3,4,5..., and the number of items in each cell is not
even.

I also tried to put the formulas at the bottom of the records to allow
more room for the indirect commend, this time some answers are correct,
but most of the answers are still 0. I have 120 records in this sheet
and I really hate to do it manually.

Thanks a lot!


--
sun1x
------------------------------------------------------------------------
sun1x's Profile:
http://www.excelforum.com/member.php...o&userid=28327
View this thread: http://www.excelforum.com/showthread...hreadid=479096