Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
code to delete sheets not in use based on a cell's drop down list | Excel Worksheet Functions | |||
Removing rows in which 1 cell's value appears on a separate list | Excel Discussion (Misc queries) | |||
Changing named Validation list to Dynamic list. | Excel Discussion (Misc queries) | |||
Remove empty cells from named list / validation list | Excel Worksheet Functions | |||
Changing named range reference depending on a cell's content | Excel Discussion (Misc queries) |