Array reference

  • Thread starter Thread starter bobj
  • Start date Start date
B

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(D1:D6;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
 
Le 25/05/2013 13:49, Ron Rosenfeld a écrit :
Try:

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

Where, in this case
Basket: D1:D6
Items: A1:A4
Prices: B1:B3
Click to expand...

Works perfect,
Many thanks,
bobj
 
Back
Top