Perform a calculation based on a cell being selected

[fluffy]

I have 6 columns with several rows of data. If I chose Column A by placing
an "x" in the last cell in the Column, I need a calculation to be peformed in
the 7th column (Cell immaterial) using the data in the Column. There will
only be one column selected at a time but it could be any of the 6 columns at
any time

Example

Column A
row 1 = 3
Row 2 = 5
row 3 = 2

if row 4 has an "x" in it I want a cell in Column 7 to do the following
calculation: 3(row 1)+5(row 2)-2(row 3)=6(calculated value)

 

[JE McGimpsey]

One way:

=INDEX(A1:F1+A2:F2-A3:F3,1,MATCH("x",A4:F4))

 

[T. Valko]

Try this:

=SUM(INDEX(A1:F2,,MATCH("x",A4:F4,0)))-INDEX(A3:F3,MATCH("x",A4:F4,0))

If there is no "x" the formula returns #N/A. If there is more than one "x"
the formula will calculate based on the leftmost "x".