Array Construction

I understand the solution of . . . .

=sumproduct(--isnumber(match(a1:100,midwest,0)),b1:b100)

Now, I am going to throw one more issue into the problem.

What if I have a third column containing text (such as "revenues" and
"expense").

I need to add all the revenues for all the midwest departments into one
cell. Or, I need to add all the expenses for all the midwest departments
into one cell.




"Mike Moore" <mmoore@apci.net> wrote in message
news:%237OX%23XerGHA.3988@TK2MSFTNGP02.phx.gbl...
> Is there a better array formula construction than the one described below?
>
> I have two columns. Column A contains department designations (i.e. dept
> A, dept B, dept C, dept D, etc). Column B contains dollar amounts.
>
> The formula parameters are to sum multiple designated departments into one
> cell.
>
> I have the following formula developed:
>
> {=sum((a1:a100=dept a)*(b1:b100))+sum((A1:a100=dept c)*(b1:b100))}
>
> Instead of having two sum statements, can the formula be condensed to one
> sum statement? For example,
>
> {=sum((a1:a100=And(dept a, dept c))*(b1:b100))}
>
> I have tried the above formula, but could not get it to work.
>
> Second question - is it possible to set up the array formula to deal with
> conditions that change? For example, department A and department C belong
> to the Midwest region. However, next year department C belongs to the
> Southwest region and department B belongs to the Midwest region.
>
> I don't want to have to change hundreds of array formulas due to
> department changes.
>
> I have thought about using named ranges that designate a list of
> departments belonging to regions. However, I have been unsuccessful in
> this approach.
>

Hi Mike

Set the Text you are looking for in a cell e.g. H1
Then assuming your column containing the text is column C
=sumproduct(--isnumber(match(a1:100,midwest,0)),--(C1:C100=H1),b1:b100)

You can keep on adding conditions into the Sumproduct formula with
further sets of
,--(range=test),

--
Regards

Roger Govier


"M Moore" <mmoore@apci.net> wrote in message
news:ed8ir$prGHA.4480@TK2MSFTNGP04.phx.gbl...
>I understand the solution of . . . .
>
> =sumproduct(--isnumber(match(a1:100,midwest,0)),b1:b100)
>
> Now, I am going to throw one more issue into the problem.
>
> What if I have a third column containing text (such as "revenues" and
> "expense").
>
> I need to add all the revenues for all the midwest departments into
> one
> cell. Or, I need to add all the expenses for all the midwest
> departments
> into one cell.
>
>
>
>
> "Mike Moore" <mmoore@apci.net> wrote in message
> news:%237OX%23XerGHA.3988@TK2MSFTNGP02.phx.gbl...
>> Is there a better array formula construction than the one described
>> below?
>>
>> I have two columns. Column A contains department designations (i.e.
>> dept
>> A, dept B, dept C, dept D, etc). Column B contains dollar amounts.
>>
>> The formula parameters are to sum multiple designated departments
>> into one
>> cell.
>>
>> I have the following formula developed:
>>
>> {=sum((a1:a100=dept a)*(b1:b100))+sum((A1:a100=dept c)*(b1:b100))}
>>
>> Instead of having two sum statements, can the formula be condensed to
>> one
>> sum statement? For example,
>>
>> {=sum((a1:a100=And(dept a, dept c))*(b1:b100))}
>>
>> I have tried the above formula, but could not get it to work.
>>
>> Second question - is it possible to set up the array formula to deal
>> with
>> conditions that change? For example, department A and department C
>> belong
>> to the Midwest region. However, next year department C belongs to
>> the
>> Southwest region and department B belongs to the Midwest region.
>>
>> I don't want to have to change hundreds of array formulas due to
>> department changes.
>>
>> I have thought about using named ranges that designate a list of
>> departments belonging to regions. However, I have been unsuccessful
>> in
>> this approach.
>>
>
>
>