I have a list of unique reference numbers in column A
In column B I have the refence numbers from column A jumbled up and the same number can appear multiple times or not at all.
In column C I mark if the reference number in column A is a Bronze or Silver Class

In column D I now need the formula to do the following calculation.
Check if the reference number in column A appears anywhere in column B, if it does check if the reference number which it appears next to is a Bronze or Silver Class. If it is Bronze add 10 if it is Silver add 20.

The refernce number in column A will only appear once in column A but multiple times (or not at all) in column B

Can anyone help with this formula

Il 02/12/2012 12:43, ha scritto:
I have a list of unique reference numbers in column A
In column B I have the refence numbers from column A jumbled up and the same number can appear multiple times or not at all.
In column C I mark if the reference number in column A is a Bronze or Silver Class

In column D I now need the formula to do the following calculation.
Check if the reference number in column A appears anywhere in column B, if it does check if the reference number which it appears next to is a Bronze or Silver Class. If it is Bronze add 10 if it is Silver add 20.

The refernce number in column A will only appear once in column A but multiple times (or not at all) in column B

Can anyone help with this formula



=IF(ISERROR(FIND(A1,B1)),"",A1+(C1="silver")*20+(C 1="bronze")*10)

Hi,
E.

On Sunday, December 2, 2012 2:17:03 PM UTC+2, plinius wrote:
Il 02/12/2012 12:43, ha scritto:

I have a list of unique reference numbers in column A

In column B I have the refence numbers from column A jumbled up and the same number can appear multiple times or not at all.

In column C I mark if the reference number in column A is a Bronze or Silver Class



In column D I now need the formula to do the following calculation.

Check if the reference number in column A appears anywhere in column B, if it does check if the reference number which it appears next to is a Bronze or Silver Class. If it is Bronze add 10 if it is Silver add 20.



The refernce number in column A will only appear once in column A but multiple times (or not at all) in column B



Can anyone help with this formula







=IF(ISERROR(FIND(A1,B1)),"",A1+(C1="silver")*20+(C 1="bronze")*10)



Hi,

E.

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

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.

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