Combine and Sum Lookups across multiple rows using an array formula

[pkh]

Hi, I need to convert several currencies across multiple rows into GBP by looking up a conversion table and then provide the sum in GBP. I haven't been able to come up with an elegant formula (an array type formula maybe) to keep it short and simple.

for the SUM in GBP, I've got the following formula:
SUM(C8/INDEX($C$2:$C$4,MATCH(B8,$B$2:$B$4,0),),C9/INDEX($C$2:$C$4,MATCH(B9,$B$2:$B$4,0),),C10/INDEX($C$2:$C$4,MATCH(B10,$B$2:$B$4,0),))

But this is not practical as the actual table of items has a lot more rows. Any help would be much appreciated.

Regards,
pkh

 

[jasonb75]

See if this works

=SUMPRODUCT($C$8:$C$10/INDEX($C$2:$C$4,MATCH(T(IF({1},$B$8:$B$10)),$B$2:$B$4,0)))

 

[pkh]

See if this works

=SUMPRODUCT($C$8:$C$10/INDEX($C$2:$C$4,MATCH(T(IF({1},$B$8:$B$10)),$B$2:$B$4,0)))

Jasonb75,

Exactly what I was looking for!!! Worked like a dream . Thank you very much.

regards,
pk

 

[Amit Tandon]

Try below formula:

=SUMPRODUCT(SUMIFS(C8:C10,B8:B10,B2:B4)/C2:C4)


Regards,
Amit Tandon

 

[pkh]

Try below formula:

=SUMPRODUCT(SUMIFS(C8:C10,B8:B10,B2:B4)/C2:C4)


Regards,
Amit Tandon
www.excelanytime.com

Amit,

Thanks for this. Also very elegant. Much appreciated.

Regards,
pkh

 

[pkh]

Amit,

Thanks for this. Also very elegant. Much appreciated.

Regards,
pkh

A

Try below formula:

=SUMPRODUCT(SUMIFS(C8:C10,B8:B10,B2:B4)/C2:C4)


Regards,
Amit Tandon
www.excelanytime.com

Amit,

Actually this worked even better because i could extend the formula to a blank row below without getting #NA errors, which I could not avoid in the index match approach without specifying a blank row in the lookup array. I don't like using ISNA as it can hide other errors.

Thanks,
pkh