Hi
Try
=SUMPRODUCT((ISNUMBER(MATCH(A1:A8,vegetable,0)))*B 1:B8)

--
Regards
Roger Govier

"YY san." <YY wrote in message
...
Hi,
Appreciate very much if someone could help me on this.
I have the following:
COL A
Pear
Celery
Cherry
Apple
Cucumber
Pear
Apple
Cucumber
:
:

COL B
18
12
13
14
9
8
5
4
:
:
and a Named Range Define as Vegetable having
Cucumber
Celery

I want to sum all rows of COL B where COL A falls in the Vegetable
list.(Ans: 25). I dont want to have something like ..."Cucumber",
"Celery"...
because this Vegetable Named Range will append over time. Therefore I want
to
use a Name Range to have this flexibility.

I believe Excel functions can do the job, hopefully not Pivot table or VB.
I am using Excel 2007. Thanks for your help! Cheers.