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

[Ann Scharpf]

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.

 

[T. Valko]

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?

 

[Ann Scharpf]

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

Dummy test:

Decision = D418
Dollars = E4:E18, G4:G18, I4:I18

Real document:

Customer Funding Category:
F:F

Material Costs:
I:I, M:M

 

[Luke M]

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.

 

[T. Valko]

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)