IF XX meets value 1 then return zero for the remaining columns

[Trig79]

Hi Guy's

simple one for you!

Have extacted data as per attached spreadsheet

Columns A - J Rows 1 - 7.
each will have a combination of data called AAA - GGG, AAA being the most important value.

I have asked to return a value of 1 (Colums H-N) when one of the conditions are met.

the next step is to then return value of 1 in columns P-V, but when value 1 is returned all other columns should reflect zero.

the idea is to capture the most important value

i have uploaded a sample sheet, as this was a little difficult to describe.

many thanks

[mike65535]

the next step is to then return value of 1 in columns P-V, but when value 1 is returned all other columns should reflect zero.

I think you are trying to say is
1) P = H (always)
2) If P is 1, then Q = 0, else Q = I
3) If P or Q is 1, then R = 0, else R = J
4) If P or Q or R is 1, then S = 0, else S = K

and so on?

[Trig79]

hi

yes you summed it up better than me

what you say is correct

thanks

[FDibbins]

TRy this, copied across and down...
=IF(SUM($O2:O2)>0,0,COUNTIF($A2:$G2,P$1))

[Trig79]

unfortunately not, it fails on row 3

[FDibbins]

How so?

I get a 1 under BBB, and all the rest are 0?

[Trig79]

sorry was about to re-post, finger trouble at my end.

It works fine, thanks for the answer, i would never have got there.

[FDibbins]

you'r welcome. If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neat and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below)