Thread: 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" <AnnScharpf@discussions.microsoft.com> wrote in message
news:30B2910B-5948-49E9-BDE8-4AE107514FE6@microsoft.com...
>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

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
--
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" <AnnScharpf@discussions.microsoft.com> wrote in message
> news:30B2910B-5948-49E9-BDE8-4AE107514FE6@microsoft.com...
> >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
>
>
> .
>

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" <AnnScharpf@discussions.microsoft.com> wrote in message
newsF7B0F66-AF58-4833-8A10-9313AD015A27@microsoft.com...
> 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
> --
> 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" <AnnScharpf@discussions.microsoft.com> wrote in message
>> news:30B2910B-5948-49E9-BDE8-4AE107514FE6@microsoft.com...
>> >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
>>
>>
>> .
>>