Named Range Help

[wtsabo]

Hello,
I am trying to use named ranges to define a group of variables that will be used in a sumproduct, but I am return errors.

For example:

Sheet 1:
Name ranges
Apple = {1.000.1, 1.000.2, 1.000.3}
Banana = {2.000.1, 2.000.2, 2.000.3, 2.000}
Carrot = {3.000.1, 3.000.2, 3.000.3, c.0000}

Sheet 2:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Value[/TD]
[TD]Group[/TD]
[/TR]
[TR]
[TD]01/02/2012[/TD]
[TD]5[/TD]
[TD]1.000.1[/TD]
[/TR]
[TR]
[TD]4/30/2012[/TD]
[TD]10[/TD]
[TD]1.000.2[/TD]
[/TR]
[TR]
[TD]5/14/2012[/TD]
[TD]15[/TD]
[TD]1.000.3[/TD]
[/TR]
[TR]
[TD]6/8/2012[/TD]
[TD]20[/TD]
[TD]3.000.1[/TD]
[/TR]
</tbody>[/TABLE]


Sheet 3:
Reference Cell = Apple
Date Range = January 1, 2012 to May 31, 2012
Results Cell = Sumproduct(((Indirect(Apple)=(Sheet 2! Group Array))*(Sheet 2! Value Array)*(Date Array<June 1,2012))
Answer = 30

The error is created by the reference for the 'apple' named range. Can someone please assist?

Thank you

 

[Andrew Poulsom]

With the named constant (not range) Apple that refers to:

={"1.000.1","1.000.2","1.000.3"}

this formula will return the number of Apples in C2:C5:

=SUMPRODUCT(--ISNUMBER(MATCH(C2:C5,Apple,FALSE)))

Maybe you can expand the formula to suit.

 

[wtsabo]

Do you mean expanding the formula for each constant?
Such as:
=sumproduct(isnumber(MATCH(C2:C5,Apple,FALSE)))*isnumber(MATCH(C2:C5,banana,FALSE)))*isnumber(MATCH(C2:C5,carrot,FALSE))--)

If so, this method is going to result in a very long/complex formula. It might be easier to adjust my data source just to create a group column using the known associations, but is there a better alternative?

 

[Andrew Poulsom]

Do you mean expanding the formula for each constant?
Such as:
=sumproduct(isnumber(MATCH(C2:C5,Apple,FALSE)))*isnumber(MATCH(C2:C5,banana,FALSE)))*isnumber(MATCH(C2:C5,carrot,FALSE))--)

No just add the other criteria that you want to the formula, eg a date range.

 

[wtsabo]

Thanks for your help