Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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" wrote in message ... 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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Array Construction
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" wrote in message ... 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" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Efficient Array Formula Construction | Excel Discussion (Misc queries) | |||
Display an array of references | Excel Worksheet Functions | |||
Positioning Numeric Values Resulting from 6 Column Array Formula | Excel Worksheet Functions | |||
Formula to list unique values | Excel Worksheet Functions | |||
VBA Import of text file & Array parsing of that data | Excel Discussion (Misc queries) |