Efficient Array Formula Construction

[Mike Moore]

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.

[daddylonglegs]

If you create a named range of Midwest departments - midwest - then try

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

which only requires ENTER

[vane0326]

See if this helps.



=SUMPRODUCT((A1:A100={"dept a","dept c"})*(B1:B100))



I use to work with a guy name Mike Moore. Did you ever worked at El Monte California?

[daddylonglegs]

....and to answer your first question, you can use the same formula without a named range, if you wish, by specifying an array of department names with the formula i.e.

=sumproduct(--isnumber(match(a1:a100,{"dept A","dept B"},0)),b1:b100)

[Sandy Mann]

To answer your question:

> 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

You were nearly there, try the array entered formua:

{=SUM((A1:A100={"Dept a","Dept c"})*(B1:B100))}

--
HTH

Sandy
In Perth, the ancient capital of Scotland

[email protected]
[email protected] with @tiscali.co.uk


"Mike Moore" <[email protected]> wrote in message
news:%237OX%[email protected]...
> 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.
>

[daddylonglegs]

Sorry, don't want to confuse you with multiple suggestions but....

This formula, which only requires ENTER is, another way to go

=SUM(SUMIF(A1:A100,{"dept a","dept b"},B1:B100))

array entered, i.e. using CTRL+SHIFT+ENTER, you can also use the above with a named range

=SUM(SUMIF(A1:A100,midwest,B1:B100))

[M Moore]

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" <[email protected]> wrote in message
news:%237OX%[email protected]...
> 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.
>

[Dav]

Just expand the previous answers
eg if revenue appears in column C
=SUMPRODUCT((A1:A100={"dept a","dept c"})*(B1:B100))

becomes
=SUMPRODUCT((A1:A100={"dept a","dept c"})*(B1:B100)*(c1:c100="Revenue"))

Regards

Dav

[daddylonglegs]

...or with your named range

=sumproduct(--isnumber(match(a1:100,midwest,0)),b1:b100,--(c1:c100="revenue"))