Help with comparing several columns and outputting a different result depending on column

[Rhuumz]

Hey all,

I dont know if my heading makes sense at all but this is what I am trying/failing to accomplish...

I have 5 columns that I want excel to check and then output a number based on the selection. The criteria in the 5 columns that I need is only that it is populated by something....

Like this...

A_____B_____C_____D_____E_____Result
x______________________________1.25
______x_________________________1
______x_________________________1
___________________x____________.5

Etc.....

So the cell in the column could be anything.. an X or a 1 or anything.... So what I would like it to do is go... is A populated if yes output 1.25.... is B populated if yes output 1.... etc... but if 2 fields are X'd then it errors

Any help is much appreciated!

[benishiryo]

hi Rhuumz, assuming data in A2:E2, try this in F2
=IF(COUNTA(A2:E2)=1,(6-MATCH(TRUE,INDEX(A2:E2<>"",),0))*0.25,"Error")

[Rhuumz]

hi Rhuumz, assuming data in A2:E2, try this in F2
=IF(COUNTA(A2:E2)=1,(6-MATCH(TRUE,INDEX(A2:E2<>"",),0))*0.25,"Error")

Hey Ben,

Thanks for that. I think you have helped me with a few items in the past! Champion

I plugged the formula in but it didnt seem to work. I will attach the file im working on

MONTHLY TRACKING TEMPLATE.xlsx

I hope I attached the correct one...

The area I am referring to is the "Membership Term" columns needs to modify the "Value" column. Each "tick" under the membership columns I need to assign a different amount so All Clubs = 1.25 Home/Renewal=1 Flexi=.75 and part=.5

Please ignore the bottom attachment I cant figure how to remove it from post