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

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

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

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

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

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

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

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

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

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

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 -