| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum values in columns based on values in named range
Hi,
hopefully i´m not in the wrong place to ask this. I have a worksheet with a named range (people) currently 6 values there, one column with lots of different values (Col D), and another column with numbers (Col G). What i want to do is to sum values in Col G based on if Col D contains any of the values in the named range "People". tried SUMIF(D:D;people;G:G) and such, but i cant figure it out. I´m probably just too stupid to see the obvious, but i really need help. Best regards, Mikael |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum values in columns based on values in named range
Hi,
Please correct for the following: 1. Replace ; with , 2. Please check the syntax - it should be =sumif(range,criteria,sum_range). 3. Looks like the range should be People. Please ensure that People is only 1 column wide. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Mikael Andersson" wrote in message ... Hi, hopefully i´m not in the wrong place to ask this. I have a worksheet with a named range (people) currently 6 values there, one column with lots of different values (Col D), and another column with numbers (Col G). What i want to do is to sum values in Col G based on if Col D contains any of the values in the named range "People". tried SUMIF(D:D;people;G:G) and such, but i cant figure it out. I´m probably just too stupid to see the obvious, but i really need help. Best regards, Mikael |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum values in columns based on values in named range
Thanks for a quick reply. However, it did not solve my problem. I´m using
excel 2007 (separates criteria with ';'), should have mentioned that, and maybe i should explain further; As i have understood it, the Range refers to the cells I want evaluated, criteria should be any of the values in ny named range, and Sum_range refers to the cells to sum if criteria is met? Can i not use sumif? Br Mikael "Ashish Mathur" wrote: Hi, Please correct for the following: 1. Replace ; with , 2. Please check the syntax - it should be =sumif(range,criteria,sum_range). 3. Looks like the range should be People. Please ensure that People is only 1 column wide. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Mikael Andersson" wrote in message ... Hi, hopefully i´m not in the wrong place to ask this. I have a worksheet with a named range (people) currently 6 values there, one column with lots of different values (Col D), and another column with numbers (Col G). What i want to do is to sum values in Col G based on if Col D contains any of the values in the named range "People". tried SUMIF(D:D;people;G:G) and such, but i cant figure it out. I´m probably just too stupid to see the obvious, but i really need help. Best regards, Mikael |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum values in columns based on values in named range
Hi,
You can use SUMIF() since you want to sum based in one criteria. Please read up on the SUMIF() function in the Help menu - Good example there. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Mikael Andersson" wrote in message ... Thanks for a quick reply. However, it did not solve my problem. I´m using excel 2007 (separates criteria with ';'), should have mentioned that, and maybe i should explain further; As i have understood it, the Range refers to the cells I want evaluated, criteria should be any of the values in ny named range, and Sum_range refers to the cells to sum if criteria is met? Can i not use sumif? Br Mikael "Ashish Mathur" wrote: Hi, Please correct for the following: 1. Replace ; with , 2. Please check the syntax - it should be =sumif(range,criteria,sum_range). 3. Looks like the range should be People. Please ensure that People is only 1 column wide. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Mikael Andersson" wrote in message ... Hi, hopefully i´m not in the wrong place to ask this. I have a worksheet with a named range (people) currently 6 values there, one column with lots of different values (Col D), and another column with numbers (Col G). What i want to do is to sum values in Col G based on if Col D contains any of the values in the named range "People". tried SUMIF(D:D;people;G:G) and such, but i cant figure it out. I´m probably just too stupid to see the obvious, but i really need help. Best regards, Mikael |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum values in columns based on values in named range
Hi, and thanks for your time. I´m familiar with the sumif function, the
problem seems to be the multiple criteria that exists in the range. I need to base the sumif on the range instead of hardcoding the criteria (easy). br mikael "Ashish Mathur" wrote: Hi, You can use SUMIF() since you want to sum based in one criteria. Please read up on the SUMIF() function in the Help menu - Good example there. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Mikael Andersson" wrote in message ... Thanks for a quick reply. However, it did not solve my problem. I´m using excel 2007 (separates criteria with ';'), should have mentioned that, and maybe i should explain further; As i have understood it, the Range refers to the cells I want evaluated, criteria should be any of the values in ny named range, and Sum_range refers to the cells to sum if criteria is met? Can i not use sumif? Br Mikael "Ashish Mathur" wrote: Hi, Please correct for the following: 1. Replace ; with , 2. Please check the syntax - it should be =sumif(range,criteria,sum_range). 3. Looks like the range should be People. Please ensure that People is only 1 column wide. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Mikael Andersson" wrote in message ... Hi, hopefully i´m not in the wrong place to ask this. I have a worksheet with a named range (people) currently 6 values there, one column with lots of different values (Col D), and another column with numbers (Col G). What i want to do is to sum values in Col G based on if Col D contains any of the values in the named range "People". tried SUMIF(D:D;people;G:G) and such, but i cant figure it out. I´m probably just too stupid to see the obvious, but i really need help. Best regards, Mikael |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum values in columns based on values in named range
This array* formula will probably do it for you:
=SUM(IF(ISNUMBER(MATCH(D1:D100,people,0)),G1:G100) ) You can't use full-column references with array formulae (unless you have Excel 2007), so adjust the ranges to suit your data. *As this is an array formula, you have to commit it using CTRL-SHIFT- ENTER (CSE) instead of the usual ENTER. If you do this correctly then Excel will wrap curly braces { } around the formula when viewed in the formula bar - do not type these yourself. If you edit/amend the formula, use CSE again. Hope this helps. Pete On Nov 11, 10:51*am, Mikael Andersson wrote: Hi, hopefully i´m not in the wrong place to ask this. I have a worksheet with a named range (people) currently 6 values there, one column with lots of different values (Col D), and another column with numbers (Col G). What i want to do is to sum values in Col G based on if Col D contains any of the values in the named range "People". tried SUMIF(D:D;people;G:G) and such, but i cant figure it out. I´m probably just too stupid to see the obvious, but i really need help.. Best regards, Mikael |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum values in columns based on values in named range
Hi,
I apologise, I misunderstood your question. You may also try to use the DSUM function for this. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Mikael Andersson" wrote in message ... Hi, and thanks for your time. I´m familiar with the sumif function, the problem seems to be the multiple criteria that exists in the range. I need to base the sumif on the range instead of hardcoding the criteria (easy). br mikael "Ashish Mathur" wrote: Hi, You can use SUMIF() since you want to sum based in one criteria. Please read up on the SUMIF() function in the Help menu - Good example there. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Mikael Andersson" wrote in message ... Thanks for a quick reply. However, it did not solve my problem. I´m using excel 2007 (separates criteria with ';'), should have mentioned that, and maybe i should explain further; As i have understood it, the Range refers to the cells I want evaluated, criteria should be any of the values in ny named range, and Sum_range refers to the cells to sum if criteria is met? Can i not use sumif? Br Mikael "Ashish Mathur" wrote: Hi, Please correct for the following: 1. Replace ; with , 2. Please check the syntax - it should be =sumif(range,criteria,sum_range). 3. Looks like the range should be People. Please ensure that People is only 1 column wide. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Mikael Andersson" wrote in message ... Hi, hopefully i´m not in the wrong place to ask this. I have a worksheet with a named range (people) currently 6 values there, one column with lots of different values (Col D), and another column with numbers (Col G). What i want to do is to sum values in Col G based on if Col D contains any of the values in the named range "People". tried SUMIF(D:D;people;G:G) and such, but i cant figure it out. I´m probably just too stupid to see the obvious, but i really need help. Best regards, Mikael |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum values in columns based on values in named range
=SUMPRODUCT(G1:G100;--(COUNTIF(People;D1:D100)<0))
My guess is you don't need to replace ; with ,. You probably have settings that list separator is the ;. HTH Kostis Vezerides On Nov 11, 12:51*pm, Mikael Andersson wrote: Hi, hopefully i´m not in the wrong place to ask this. I have a worksheet with a named range (people) currently 6 values there, one column with lots of different values (Col D), and another column with numbers (Col G). What i want to do is to sum values in Col G based on if Col D contains any of the values in the named range "People". tried SUMIF(D:D;people;G:G) and such, but i cant figure it out. I´m probably just too stupid to see the obvious, but i really need help.. Best regards, Mikael |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum values in columns based on values in named range
THANK YOU!
that solved the issue (i´m using excel 2007 btw) is there an easier way to do this with 2007? However, thanks a million! Br Mikael "Pete_UK" wrote: This array* formula will probably do it for you: =SUM(IF(ISNUMBER(MATCH(D1:D100,people,0)),G1:G100) ) You can't use full-column references with array formulae (unless you have Excel 2007), so adjust the ranges to suit your data. *As this is an array formula, you have to commit it using CTRL-SHIFT- ENTER (CSE) instead of the usual ENTER. If you do this correctly then Excel will wrap curly braces { } around the formula when viewed in the formula bar - do not type these yourself. If you edit/amend the formula, use CSE again. Hope this helps. Pete On Nov 11, 10:51 am, Mikael Andersson wrote: Hi, hopefully i´m not in the wrong place to ask this. I have a worksheet with a named range (people) currently 6 values there, one column with lots of different values (Col D), and another column with numbers (Col G). What i want to do is to sum values in Col G based on if Col D contains any of the values in the named range "People". tried SUMIF(D:D;people;G:G) and such, but i cant figure it out. I´m probably just too stupid to see the obvious, but i really need help.. Best regards, Mikael |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum values in columns based on values in named range
Thanks again, this is another helpful solution.
gone from none to 2 working solutions in one day! i really appreciate this, BR Mikael "vezerid" wrote: =SUMPRODUCT(G1:G100;--(COUNTIF(People;D1:D100)<0)) My guess is you don't need to replace ; with ,. You probably have settings that list separator is the ;. HTH Kostis Vezerides On Nov 11, 12:51 pm, Mikael Andersson wrote: Hi, hopefully i´m not in the wrong place to ask this. I have a worksheet with a named range (people) currently 6 values there, one column with lots of different values (Col D), and another column with numbers (Col G). What i want to do is to sum values in Col G based on if Col D contains any of the values in the named range "People". tried SUMIF(D:D;people;G:G) and such, but i cant figure it out. I´m probably just too stupid to see the obvious, but i really need help.. Best regards, Mikael |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum values in columns based on values in named range
You're welcome - glad to help.
Pete On Nov 12, 9:27*am, Mikael Andersson wrote: THANK YOU! that solved the issue (i´m using excel 2007 btw) is there an easier way to do this with 2007? However, thanks a million! Br Mikael "Pete_UK" wrote: This array* formula will probably do it for you: =SUM(IF(ISNUMBER(MATCH(D1:D100,people,0)),G1:G100) ) You can't use full-column references with array formulae (unless you have Excel 2007), so adjust the ranges to suit your data. *As this is an array formula, you have to commit it using CTRL-SHIFT- ENTER (CSE) instead of the usual ENTER. If you do this correctly then Excel will wrap curly braces { } around the formula when viewed in the formula bar - do not type these yourself. If you edit/amend the formula, use CSE again. Hope this helps. Pete On Nov 11, 10:51 am, Mikael Andersson wrote: Hi, hopefully i´m not in the wrong place to ask this. I have a worksheet with a named range (people) currently 6 values there, one column with lots of different values (Col D), and another column with numbers (Col G). What i want to do is to sum values in Col G based on if Col D contains any of the values in the named range "People". tried SUMIF(D:D;people;G:G) and such, but i cant figure it out. I´m probably just too stupid to see the obvious, but i really need help.. Best regards, Mikael- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Named range not continuous. How to display values? | Excel Worksheet Functions | |||
| Named range changes values | Excel Discussion (Misc queries) | |||
| Add up a column based on values in other columns | Excel Worksheet Functions | |||
| looking up values in 1 column based on 2 other columns | Excel Worksheet Functions | |||
| Counting based on values in two columns? | Excel Worksheet Functions | |||
Linear Mode
