On Sunday, December 2, 2012 4:26:08 PM UTC+2, plinius wrote:
Il 02/12/2012 14:01, ha scritto:



Thanks for the reply but this only finds it once. This is the table

A B C D

1456 1457 Bronze

1457 1456 Silver

1458 1456 Silver

1459 1457 Bronze

1460 1465 Bronze

1461 1456 Bronze

1462 1457 Silver

1463 1454 Silver

1464 1457 Silver

1465 1454 Silver

1466 1465 Bronze

1467 1464 Silver

1468 1457 Silver

As you can see in column D next to reference number 1456 I need to check wherever the number 1456 appears , then check if it is in a row which is Bronze or Silver and sum the value of either 10 or 20. I suspect this is an array formula. This formula then needs to be copied down the whole column D





I misunderstood all :-(



1456 1457 Bronze 50

1457 1456 Silver 80

1458 1456 Silver 0

1459 1457 Bronze 0

1460 1465 Bronze 0

1461 1456 Bronze 0

1462 1457 Silver 0

1463 1454 Silver 0

1464 1457 Silver 20

1465 1454 Silver 20

1466 1465 Bronze 0

1467 1464 Silver 0

1468 1457 Silver 0



If you need in column D that value, try this:



=SUMPRODUCT(($B$1:$B$13=A1)*(($C$1:$C$13="silver") *20+($C$1:$C$13="bronze")*10))



Hi

E.

Thank you, This works perfectly