Using Defined Name in a SUMPRODUCT

[rico90210]

Hi XL users,

I am trying to evaluate a simple SUMPRODUCT between 2 rows. But one of the row contains an array of Defined Names (each one being linked to a single numeric value). I have used the INDIRECT function to recall the numbers related to the Defined Names but I understand that INDIRECT is not managing well this array and thus the SUMPRODUCT return an error.
= SUMPRODUCT (A1:C1 , INDIRECT ("A2:C2"))

where A2:C2 contains respectively {EUR,GBP,USD} names defined as exchange rate of the day (single numeric value in name manager).

Any idea ?

 

[Hui]

Rico

can you post a sample file and a sample of what you expected?

 

[rico90210]

Yes sure.

As the result of the formula I would expect to have:
1.13*10+1.46*20+1.00*50=90.5

 

[Hui]

Your math is wrong it should be
1.13*10+1.46*30+1.00*50=105.1

Which is solved using the formula:
=SUMPRODUCT((A8:C8)*N(INDIRECT(INDIRECT("A7:C7"))))

see attached file:

 

[rico90210]

Works fine, problem solved
Thanks Hui

 

[NARAYANK991]

Hi ,

As your list of currencies grows , it may be cumbersome to define each currency ; in this situation , you can make use of the technique given in the attached file.

Narayan

 

[bosco_yip]

Another approach in not using Defined Name,

try this shorter array formula (Confirm pressing with SHIFT+CTRL+ENTER)

=SUM(LOOKUP(TRANSPOSE(A7:C7),A2:B4)*TRANSPOSE(A8:C8))

Regards
Bosco

 

[bines53]

Hello friends,

It is also possible that,
=MMULT(A8:C8,LOOKUP(TRANSPOSE(A7:C7),A2:B4))

(Confirm pressing with SHIFT+CTRL+ENTER)

=SUMPRODUCT(A8:C8*LOOKUP(A7:C7,A2:B4))

without SCE

David