Substitute text with assigned values

[Guest]

I have a table as below:

A B C D E
1 Value 2 9 5 Total
2 Item 1 x
3 Item 2 x x
4 Item 3 x
5 Item 4

Whereever x is plotted, I need the cell to be assigned values from the
corresponding row above.
Eg. Item 2 is marked x in col B, I want the cell B3 to have a value 2 even
though x can be seen. Similarly item 2 is marked x in Col D, should now have
a value 5.
I need to sum up these values in the total column E.

Do reply.

 

[Guest]

Perhaps something like this:

E2: =SUMIF(B22,"x",B$1$1)
copy that formula down as far as needed.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

 

[Guest]

If you want a result like the example below, you can use this formula
starting in E2 and copying down.

=SUMPRODUCT((B22="x")*$B$1:$D$1)

A B C D E
1 Value 2 9 5 Total
2 Item 1 x 9
3 Item 2 x x 7
4 Item 3 x 9
5 Item 4 x 2
6 Item 5 x x 11

If you want to actually change the values of the cells with x's to the
appropriate number, you will need a macro. Why don't you ask in the
Excel->Programming forum?