SUMIF() to add cells in non-contiguous ranges? (Excel 2003)
 

I have a bunch of columns with dollar values. I want to add the sum of all
the values for rows that meet a single condition. If possible, I'd like to
create a named a range that includes all the non-contiguous dollar value
columns and use a single SUMIF(). In my test, I named this range DOLLARS.

The formula =SUM(DOLLARS) does work fine and adds up ALL the values.

To test the SUMIF(), I set up a column (named range "decision") with yes/no
values. I get a #VALUE error when I try the formula
=SUMIF(decision,"=yes",dollars).

I looked at some of the posts for array functions and I can't really tell if
an array formula would fix this problem. Is this possible to do as a single
function, or do I have to have something like this:

=SUMIF(decision,"=yes",dollars1)+SUMIF(decision,"= yes",dollars2)...

Thanks for your help.
--
Ann Scharpf

SUMIF() to add cells in non-contiguous ranges? (Excel 2003)
 

In my test, I named this range DOLLARS.

What are the individual range addresses that make up DOLLARS?

I set up a column (named range "decision") with yes/no

What is the range address that makes up DECISION?

--
Biff
Microsoft Excel MVP


"Ann Scharpf" wrote in message
...
I have a bunch of columns with dollar values. I want to add the sum of all
the values for rows that meet a single condition. If possible, I'd like
to
create a named a range that includes all the non-contiguous dollar value
columns and use a single SUMIF(). In my test, I named this range DOLLARS.

The formula =SUM(DOLLARS) does work fine and adds up ALL the values.

To test the SUMIF(), I set up a column (named range "decision") with
yes/no
values. I get a #VALUE error when I try the formula
=SUMIF(decision,"=yes",dollars).

I looked at some of the posts for array functions and I can't really tell
if
an array formula would fix this problem. Is this possible to do as a
single
function, or do I have to have something like this:

=SUMIF(decision,"=yes",dollars1)+SUMIF(decision,"= yes",dollars2)...

Thanks for your help.
--
Ann Scharpf

SUMIF() to add cells in non-contiguous ranges? (Excel 2003)
 

Well, my dummy test and the real document are set up differently.

Dummy test:

Decision = D4:D18
Dollars = E4:E18, G4:G18, I4:I18

Real document:

Customer Funding Category:
F:F

Material Costs:
I:I, M:M
--
Ann Scharpf


"T. Valko" wrote:

In my test, I named this range DOLLARS.

What are the individual range addresses that make up DOLLARS?

I set up a column (named range "decision") with yes/no

What is the range address that makes up DECISION?

--
Biff
Microsoft Excel MVP


"Ann Scharpf" wrote in message
...
I have a bunch of columns with dollar values. I want to add the sum of all
the values for rows that meet a single condition. If possible, I'd like
to
create a named a range that includes all the non-contiguous dollar value
columns and use a single SUMIF(). In my test, I named this range DOLLARS.

The formula =SUM(DOLLARS) does work fine and adds up ALL the values.

To test the SUMIF(), I set up a column (named range "decision") with
yes/no
values. I get a #VALUE error when I try the formula
=SUMIF(decision,"=yes",dollars).

I looked at some of the posts for array functions and I can't really tell
if
an array formula would fix this problem. Is this possible to do as a
single
function, or do I have to have something like this:

=SUMIF(decision,"=yes",dollars1)+SUMIF(decision,"= yes",dollars2)...

Thanks for your help.
--
Ann Scharpf


.

SUMIF() to add cells in non-contiguous ranges? (Excel 2003)
 

What I think Biff was asking about was what is the logic behind the
non-contiguous cell choices?
As an quick example, this takes the sum of every 4th row that has a
corresponding text of "Add"

=SUMPRODUCT(--(NOT(MOD(ROW(A1:A20),4))),--(B1:B20="add"),(A1:A20))

Then you can get away from the use of named ranges.
--
Best Regards,

Luke M
"Ann Scharpf" wrote in message
...
Well, my dummy test and the real document are set up differently.

Dummy test:

Decision = D4:D18
Dollars = E4:E18, G4:G18, I4:I18

Real document:

Customer Funding Category:
F:F

Material Costs:
I:I, M:M
--
Ann Scharpf


"T. Valko" wrote:

In my test, I named this range DOLLARS.

What are the individual range addresses that make up DOLLARS?

I set up a column (named range "decision") with yes/no

What is the range address that makes up DECISION?

--
Biff
Microsoft Excel MVP


"Ann Scharpf" wrote in message
...
I have a bunch of columns with dollar values. I want to add the sum of
all
the values for rows that meet a single condition. If possible, I'd
like
to
create a named a range that includes all the non-contiguous dollar
value
columns and use a single SUMIF(). In my test, I named this range
DOLLARS.

The formula =SUM(DOLLARS) does work fine and adds up ALL the values.

To test the SUMIF(), I set up a column (named range "decision") with
yes/no
values. I get a #VALUE error when I try the formula
=SUMIF(decision,"=yes",dollars).

I looked at some of the posts for array functions and I can't really
tell
if
an array formula would fix this problem. Is this possible to do as a
single
function, or do I have to have something like this:

=SUMIF(decision,"=yes",dollars1)+SUMIF(decision,"= yes",dollars2)...

Thanks for your help.
--
Ann Scharpf


.

SUMIF() to add cells in non-contiguous ranges? (Excel 2003)
 

Well, the non-contiguous range DOLLARS presents a problem.

Just because a range has a defined name doesn't mean you *have* to use that
name!

Here's how I would do it...

=SUMPRODUCT(--(Decision="yes"),E4:E18+G4:G18+I4:I18)

--
Biff
Microsoft Excel MVP


"Ann Scharpf" wrote in message
...
Well, my dummy test and the real document are set up differently.

Dummy test:

Decision = D4:D18
Dollars = E4:E18, G4:G18, I4:I18

Real document:

Customer Funding Category:
F:F

Material Costs:
I:I, M:M
--
Ann Scharpf


"T. Valko" wrote:

In my test, I named this range DOLLARS.

What are the individual range addresses that make up DOLLARS?

I set up a column (named range "decision") with yes/no

What is the range address that makes up DECISION?

--
Biff
Microsoft Excel MVP


"Ann Scharpf" wrote in message
...
I have a bunch of columns with dollar values. I want to add the sum of
all
the values for rows that meet a single condition. If possible, I'd
like
to
create a named a range that includes all the non-contiguous dollar
value
columns and use a single SUMIF(). In my test, I named this range
DOLLARS.

The formula =SUM(DOLLARS) does work fine and adds up ALL the values.

To test the SUMIF(), I set up a column (named range "decision") with
yes/no
values. I get a #VALUE error when I try the formula
=SUMIF(decision,"=yes",dollars).

I looked at some of the posts for array functions and I can't really
tell
if
an array formula would fix this problem. Is this possible to do as a
single
function, or do I have to have something like this:

=SUMIF(decision,"=yes",dollars1)+SUMIF(decision,"= yes",dollars2)...

Thanks for your help.
--
Ann Scharpf


.