Sum values in columns based on values in named range

[Mikael Andersson]

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;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

 

[Ashish Mathur]

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]

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]

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]

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

 

[Pete_UK]

This array* formula will probably do it for you:

=SUM(IF(ISNUMBER(MATCH(D1100,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

 

[Ashish Mathur]

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

 

[vezerid]

=SUMPRODUCT(G1:G100;--(COUNTIF(People;D1100)<>0))

My guess is you don't need to replace ; with ,. You probably have
settings that list separator is the ;.

HTH
Kostis Vezerides

 

[Mikael Andersson]

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

 

[Mikael Andersson]

Thanks again, this is another helpful solution.
gone from none to 2 working solutions in one day!

i really appreciate this,

BR

Mikael

 

[Pete_UK]

You're welcome - glad to help.

Pete

THANK YOU!

that solved the issue (i´m using excel 2007 btw) is there an easier wayto
do this with 2007? However, thanks a million!

Br

Mikael







- Show quoted text -