M
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.
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.