Calculate total by row number

[brianfosterblack]

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

 

[plinius]

Il 02/12/2012 12:43, (e-mail address removed) 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+(C1="bronze")*10)

Hi,
E.

 

[brianfosterblack]

Il 02/12/2012 12:43, (e-mail address removed) ha scritto:










=IF(ISERROR(FIND(A1,B1)),"",A1+(C1="silver")*20+(C1="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

 

[plinius]

Il 02/12/2012 14:01, (e-mail address removed) 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.

 

[brianfosterblack]

Il 02/12/2012 14:01, (e-mail address removed) ha scritto:






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