LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default 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.


 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Array Formula Not Working with Range with Formulas [email protected] Excel Discussion (Misc queries) 4 February 1st 06 02:01 PM
Array formula returning wrong results TUNGANA KURMA RAJU Excel Discussion (Misc queries) 1 November 19th 05 10:29 AM
referencing the value of a cell containing an array formula KR Excel Worksheet Functions 4 July 5th 05 06:15 PM
Match / Vlookup within an Array formula Hari Prasadh Excel Discussion (Misc queries) 3 February 3rd 05 04:37 PM
What instead of an array formula part 2 Reg Besseling Excel Discussion (Misc queries) 2 December 10th 04 07:35 AM


All times are GMT +1. The time now is 07:18 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"