Sum when cell's value in Named List

  • Thread starter Thread starter YY san.
  • Start date Start date
Y

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.
 
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
 
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.
 
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!
 
Back
Top