Sum when cell's value in Named List
 

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.

Sum when cell's value in Named List
 

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.

Sum when cell's value in Named List
 

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


"YY san." <YY skrev i meddelelsen
...
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.

Sum when cell's value in Named List
 

Might help to make the range absolute if copying down

--
__________________________________
HTH

Bob

"Per Jessen" wrote in message
...
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


"YY san." <YY skrev i meddelelsen
...
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.

Sum when cell's value in Named List
 

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.

Sum when cell's value in Named List
 

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!

"Spiky" wrote:

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.

Sum when cell's value in Named List
 

My suggestion was nothing to do with that.

--
__________________________________
HTH

Bob

"YY san." wrote in message
...
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!

"Spiky" wrote:

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.