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:

Date	Value	Group
01/02/2012	5	1.000.1
4/30/2012	10	1.000.2
5/14/2012	15	1.000.3
6/8/2012	20	3.000.1

<tbody>
</tbody>

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