Dynamic array constants

**michalrosa** · 07-01-2010, 08:21 PM

Greeting all!

Is it possible to create a dynamic array constant? For example lets say we have the following array formula =if(type={A,B,C},if(category={X,Y,Z},if(mode=”No”,value)))

It works fine with pre-defined, hard-coded array constants, is it possible to create them as dynamic entities, for example defined as a string in some cell and to point to that cell, something like =if(type=A1,if(category=A2,if(mode=”No”,value))) where A1 and A2 would be array constants.

Cheers,
Michal

**shg** · 07-01-2010, 08:23 PM

Give a specific example of how you're using that construct.

**michalrosa** · 07-01-2010, 08:27 PM

Originally Posted by shg

Give a specific example of how you're using that construct.

Damn, the solution I "found" does not actually work in my case - http://www.ozgrid.com/forum/showthread.php?t=46593, pity.

Not sure how a specific example can actually be of any use since I’m asking about a general rule. Anyway, a specific example you want – a specific example I can provide:

Cells A1:A3 contain values 5000,5001,5002 and are named “codes”, cells B1:B3 contain values 1000,2000,4000 and are named “values”.

Formula =SUM(IF(codes={5000,5001},values)) returns 3000 as expected, what I would like to do is to use a dynamic name instead of an array constant, so far =SUM(IF(codes=A1:A2,values)) failed, so did =SUM(IF(codes=cbd,values)) – cbd being defined as a named range A1:A2.

Cheers,
Michal

**DonkeyOte** · 07-02-2010, 04:01 AM

First up - to avoid confusion - array constants { } are just that, constants rather than variables.

In this case you are I think saying you wish to replace constants with range references such that the contents can be amended freely without need for altering dependent formulae, correct ?

The formula you mention:

=SUM(IF(codes=A1:A2,values))
confirmed with CTRL + SHIFT + ENTER

would work if revised to:

=SUM(IF(codes=TRANSPOSE(A1:A2),values))
confirmed with CTRL + SHIFT + ENTER

However, you might find the below a little more intuitive:

=SUMPRODUCT(SUMIF(codes,A1:A2,values))
confirmed with Enter

The above simply SUMs the SUMIF results for code A1 & A2.