Sum when cell's value in Named List

[YY san.]

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.

 

[Roger Govier]

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

 

[Per Jessen]

Hi

In column E enter enter your list (Cucumber, Celery...) In F1 enter this
formula:

=SUMIF(A1:A30,E1,B1:B30) and copy the formula down.

Regards,
Per

 

[Bob Phillips]

Might help to make the range absolute if copying down

 

[Spiky]

If you are willing to set it up properly, you can use the Dxxx
formulas which are very fast. And since you need a named range of your
fruits and veggies, anyway...

So, assuming "Product" is the header of your Col A (you need a
header), enter the following:
BA1: "Fruit"
BB1: "Vegetable"
BA2: "Product"
BB2: "Product"
BA3-BAxx: List of Fruits
BB3-BBxx: List of Vegetables.

Named ranges to create, note two are dynamic, you'll have to type or
paste them into the Name dialog box:
A1:B10000 is named "Data"
=$BA$2:OFFSET($BA$2,,,COUNTA(BA:BA)-1) is named "Fruit"
=$BB$2:OFFSET($BB$2,,,COUNTA(BB:BB)-1) is named "Vegetable"

Formula to add up stuff:
=DSUM(Data,2,Vegetable)
=DSUM(Data,2,Fruit)

You can add/subtract fruits and veggies to your lists and won't have
to change anything in the DSUM. Just don't leave any empty cells,
screws up the dynamic names and therefore everything.

 

[YY san.]

Thanks Roger for the first solution. It is exactly what I am looking for.
As for Bob's suggestion, I have tried it before. But because this worksheet
that I am working on, it is a "working" worksheet - no formating, very very
"raw". That's why I have no intention to set up the database tables with
proper headers.
Anyhow, thanks for all the responses. Have a great day!

 

[Bob Phillips]

My suggestion was nothing to do with that.