Arrays & array formulas

**cmcgath** · 02-06-2009, 06:48 PM

From John Walkenbach's Excel 2002 Power Programming with VBA, I finally found a way to effectively pull a number from a database that matches criteria using an array formula:

{=SUM((DB_VARIABLE=$CB$51)*(DB_YEAR=CA$5)*(DB_JANVALUES)}

What I was wondering about the array formula is whether there is a way to avoid having to name a separate array for each month, as well as DB_VARIABLE and DB_YEAR, for a total of 14 arrays.

(In 1-2-3, I can use the @DGET function to do this putting the criteria directly into the function and all I have to do is name the database range with column headings in one step.)

Or can anyone think of a better way to obtain the value I want from the database using Excel?

**DonkeyOte** · 02-06-2009, 06:51 PM

You don't have to use named ranges but without knowing to which ranges your names refer it's hard to give an exact answer... you could have a further test to check for month rather than having a definitive range for that month... often it's better to use a SUMPRODUCT rather than a SUM CSE Array, ie

=SUMPRODUCT((DB_Variable=$CB$51)*(DB_YEAR=CA$5)*(DB_JANVALUES))

The above does not require CTRL + SHIFT + ENTER

If you could detail the ranges in use and their contents we can provide a solution.

**NoLimit** · 02-06-2009, 07:05 PM

Hi,
I used to work with array formulas (also inspired from John's page), until I came accross the Cellworxs addin for Excel. The free version allows lookup functions with "WHERE" and "LIKE" criteria (in old Excel 2003 up to 20+ criteria, in XL2007 up to 300+ criteria :-) ). The commercial version allows in addition hirachy aggregation. They have a also a demo video on their home page cellworxs.com

For me this is absolute leading edge.

Cheers,
NoLimit