Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Efficient Array Formula Construction
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
|
|||
|
|||
Efficient Array Formula Construction
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 -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=564037 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Efficient Array Formula Construction
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? -- vane0326 ------------------------------------------------------------------------ vane0326's Profile: http://www.excelforum.com/member.php...o&userid=14731 View this thread: http://www.excelforum.com/showthread...hreadid=564037 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Efficient Array Formula Construction
....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) -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=564037 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Efficient Array Formula Construction
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)) -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=564037 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Efficient Array Formula 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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Efficient Array Formula Construction
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 -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=564037 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Efficient Array Formula Construction
...or with your named range =sumproduct(--isnumber(match(a1:100,midwest,0)),b1:b100,--(c1:c100="revenue")) -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=564037 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array Formula Not Working with Range with Formulas | Excel Discussion (Misc queries) | |||
Array formula returning wrong results | Excel Discussion (Misc queries) | |||
referencing the value of a cell containing an array formula | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
What instead of an array formula part 2 | Excel Discussion (Misc queries) |