Seaching the column for a value

[abhi1693r]

Hi 

I have 5 columns A-E, with values 'X' or blank. (Snapshot attached)

Now I want to check if even one 'X' present in one column, add 10 for that column in a new expression.

I am using if(A='X',10,0) + if(B='X',10,0) ........

Somehow it is only working for one of the columns. Does this not search the entire column?

[Anil_Babu_Samineni]

How about this?

If(Index(Col1&'|'&Col2&'|'&Col3&'|'&Col4, 'X'), 10, 'No X Present') as New_Column

[Anonymous]

If this is on script, instead searching for 'X' try to not find this. Do a resident load

If(Index(Column1, 'X'), 1, 0) as _fgColumn1,
If(Index(Column2, 'X'), 1, 0) as _fgColumn2,
If(Index(Column3, 'X'), 1, 0) as _fgColumn3,
If(Index(Column4, 'X'), 1, 0) as _fgColumn4,
If(Index(Column5, 'X'), 1, 0) as _fgColumn5

Then make a total sum of each _fgColumnY, save on a variable.

Then if the varaible is 0, keep the value of column (ColumnY as newColumnY); if not, add 10 (Column+10 as newColumnY)

[abhi1693r]

I guess this will only work if X is present as a character value in Excel, right?