Array reference

[bobj]

Hi,

Let says we have columns A and B (fruit, price)
Apple 10
Peach 12
Bananas 6
Other 18

Column D (Basket)
Apple
Apple
Apple
Other
Peach
Other

How compute the sum of price without intermediate column
(expected result = 70 (10+10+10+18+12+10)
I try
{=SUM(OFFSET(B1;MATCH(D16;A1:A4;0)-1)}
But it doesn't work (Maybe offset doesn't work with formula array ?)

I know how to do that with an intermediate column but I would to compute
it without this intermediate column

thanks

 

[Ron Rosenfeld]

Hi,

Let says we have columns A and B (fruit, price)
Apple 10
Peach 12
Bananas 6
Other 18

Column D (Basket)
Apple
Apple
Apple
Other
Peach
Other

How compute the sum of price without intermediate column
(expected result = 70 (10+10+10+18+12+10)
I try
{=SUM(OFFSET(B1;MATCH(D16;A1:A4;0)-1)}
But it doesn't work (Maybe offset doesn't work with formula array ?)

I know how to do that with an intermediate column but I would to compute
it without this intermediate column

thanks

Try:

=SUMPRODUCT(COUNTIF(Basket;Items)*Prices)

Where, in this case
Basket: D16
Items: A1:A4
Prices: B1:B3

 

[bobj]

Le 25/05/2013 13:49, Ron Rosenfeld a écrit :

Try:

=SUMPRODUCT(COUNTIF(Basket;Items)*Prices)

Where, in this case
Basket: D16
Items: A1:A4
Prices: B1:B3

Works perfect,
Many thanks,
bobj

 

[Ron Rosenfeld]

Le 25/05/2013 13:49, Ron Rosenfeld a écrit :

Works perfect,
Many thanks,
bobj

Glad to help. Thanks for the feedback.
-- Ron