Tough conditional formatting question

Hi all,
I have a sequence of five columns, they look something like this:

Criteria X Y Z SUM
a 0 1 3 4
b 1 0 5 6
c 7 9 0 16
d 0 0 2 2
e 0 7 0 7
f 4 0 0 4

Is there a way to use conditional formatting to mark the entry in the
criteria column for these conditions:
Nonzero number only in first column
Nonzero number only in second column
Nonzero number only in third column
Nonzero number only in first and second column
Nonzero number only in first and third column
Nonzero number only in second and third column

I know this is kind of a complex question, thanks in advance!

PS, thanks so much to the person who helped me with my last question if he
reads this, I was sick for a day and I couldn't get back here, then I
couldn't find my old post to thank you!

jezzica85

Unless I'm missing something, wouldn't this work?:

Select A2
FormatConditional FormattingCondition 1
Cell Formula is: =COUNTIF(B22,0)0
Click the [Format...] button and set the format you want

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"jezzica85" wrote:

Hi all,
I have a sequence of five columns, they look something like this:

Criteria X Y Z SUM
a 0 1 3 4
b 1 0 5 6
c 7 9 0 16
d 0 0 2 2
e 0 7 0 7
f 4 0 0 4

Is there a way to use conditional formatting to mark the entry in the
criteria column for these conditions:
Nonzero number only in first column
Nonzero number only in second column
Nonzero number only in third column
Nonzero number only in first and second column
Nonzero number only in first and third column
Nonzero number only in second and third column

I know this is kind of a complex question, thanks in advance!

PS, thanks so much to the person who helped me with my last question if he
reads this, I was sick for a day and I couldn't get back here, then I
couldn't find my old post to thank you!

jezzica85

you can't do this with conditional formatting, because you can only have up
to three conditions (four if you include the default condition).

You could insert a column inbetween columns A and B, and insert this formula
=IF(C10,"X","")&IF(D10,"Y","")&IF(E10,"Z","" )
It would look like this

Criteria XYZ X Y Z SUM
a YZ 0 1 3 4
b XZ 1 0 5 6
c XY 7 9 0 16
d Z 0 0 2 2
e Y 0 7 0 7
f X 4 0 0 4

"jezzica85" wrote:

Hi all,
I have a sequence of five columns, they look something like this:

Criteria X Y Z SUM
a 0 1 3 4
b 1 0 5 6
c 7 9 0 16
d 0 0 2 2
e 0 7 0 7
f 4 0 0 4

Is there a way to use conditional formatting to mark the entry in the
criteria column for these conditions:
Nonzero number only in first column
Nonzero number only in second column
Nonzero number only in third column
Nonzero number only in first and second column
Nonzero number only in first and third column
Nonzero number only in second and third column

I know this is kind of a complex question, thanks in advance!

PS, thanks so much to the person who helped me with my last question if he
reads this, I was sick for a day and I couldn't get back here, then I
couldn't find my old post to thank you!

jezzica85

I'd like to have a different format for each of those conditions I put down,
not the same one for each. Would this formula do that? I've never seen
something like that 0 after the parentheses before, what does that do?
Thanks!

"Ron Coderre" wrote:

Unless I'm missing something, wouldn't this work?:

Select A2
FormatConditional FormattingCondition 1
Cell Formula is: =COUNTIF(B22,0)0
Click the [Format...] button and set the format you want

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"jezzica85" wrote:

Hi all,
I have a sequence of five columns, they look something like this:

Criteria X Y Z SUM
a 0 1 3 4
b 1 0 5 6
c 7 9 0 16
d 0 0 2 2
e 0 7 0 7
f 4 0 0 4

Is there a way to use conditional formatting to mark the entry in the
criteria column for these conditions:
Nonzero number only in first column
Nonzero number only in second column
Nonzero number only in third column
Nonzero number only in first and second column
Nonzero number only in first and third column
Nonzero number only in second and third column

I know this is kind of a complex question, thanks in advance!

PS, thanks so much to the person who helped me with my last question if he
reads this, I was sick for a day and I couldn't get back here, then I
couldn't find my old post to thank you!

jezzica85

No offence Ron, but this won't work. the formula counts the number of times
0 appears in the three columns, and compares that count to 0. If the count
is greater than zero, then the formula results in TRUE and triggers the
conditional format. So basically you will get the same format for all cases
except when all three columns are non-zero.

"jezzica85" wrote:

I'd like to have a different format for each of those conditions I put down,
not the same one for each. Would this formula do that? I've never seen
something like that 0 after the parentheses before, what does that do?
Thanks!

"Ron Coderre" wrote:

Unless I'm missing something, wouldn't this work?:

Select A2
FormatConditional FormattingCondition 1
Cell Formula is: =COUNTIF(B22,0)0
Click the [Format...] button and set the format you want

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"jezzica85" wrote:

Hi all,
I have a sequence of five columns, they look something like this:

Criteria X Y Z SUM
a 0 1 3 4
b 1 0 5 6
c 7 9 0 16
d 0 0 2 2
e 0 7 0 7
f 4 0 0 4

Is there a way to use conditional formatting to mark the entry in the
criteria column for these conditions:
Nonzero number only in first column
Nonzero number only in second column
Nonzero number only in third column
Nonzero number only in first and second column
Nonzero number only in first and third column
Nonzero number only in second and third column

I know this is kind of a complex question, thanks in advance!

PS, thanks so much to the person who helped me with my last question if he
reads this, I was sick for a day and I couldn't get back here, then I
couldn't find my old post to thank you!

jezzica85

Thanks a lot, Sloth, that worked great!

"Sloth" wrote:

you can't do this with conditional formatting, because you can only have up
to three conditions (four if you include the default condition).

You could insert a column inbetween columns A and B, and insert this formula
=IF(C10,"X","")&IF(D10,"Y","")&IF(E10,"Z","" )
It would look like this

Criteria XYZ X Y Z SUM
a YZ 0 1 3 4
b XZ 1 0 5 6
c XY 7 9 0 16
d Z 0 0 2 2
e Y 0 7 0 7
f X 4 0 0 4

"jezzica85" wrote:

Hi all,
I have a sequence of five columns, they look something like this:

Criteria X Y Z SUM
a 0 1 3 4
b 1 0 5 6
c 7 9 0 16
d 0 0 2 2
e 0 7 0 7
f 4 0 0 4

Is there a way to use conditional formatting to mark the entry in the
criteria column for these conditions:
Nonzero number only in first column
Nonzero number only in second column
Nonzero number only in third column
Nonzero number only in first and second column
Nonzero number only in first and third column
Nonzero number only in second and third column

I know this is kind of a complex question, thanks in advance!

PS, thanks so much to the person who helped me with my last question if he
reads this, I was sick for a day and I couldn't get back here, then I
couldn't find my old post to thank you!

jezzica85

Understood, Sloth... No offense taken. I just wanted to make sure the OP
didn't want the same CF for each case (which effectively summarized to a zero
in any of the columns) since no list of specific formats was mentioned.

***********
Regards,
Ron

XL2002, WinXP-Pro


"Sloth" wrote:

No offence Ron, but this won't work. the formula counts the number of times
0 appears in the three columns, and compares that count to 0. If the count
is greater than zero, then the formula results in TRUE and triggers the
conditional format. So basically you will get the same format for all cases
except when all three columns are non-zero.

"jezzica85" wrote:

I'd like to have a different format for each of those conditions I put down,
not the same one for each. Would this formula do that? I've never seen
something like that 0 after the parentheses before, what does that do?
Thanks!

"Ron Coderre" wrote:

Unless I'm missing something, wouldn't this work?:

Select A2
FormatConditional FormattingCondition 1
Cell Formula is: =COUNTIF(B22,0)0
Click the [Format...] button and set the format you want

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"jezzica85" wrote:

Hi all,
I have a sequence of five columns, they look something like this:

Criteria X Y Z SUM
a 0 1 3 4
b 1 0 5 6
c 7 9 0 16
d 0 0 2 2
e 0 7 0 7
f 4 0 0 4

Is there a way to use conditional formatting to mark the entry in the
criteria column for these conditions:
Nonzero number only in first column
Nonzero number only in second column
Nonzero number only in third column
Nonzero number only in first and second column
Nonzero number only in first and third column
Nonzero number only in second and third column

I know this is kind of a complex question, thanks in advance!

PS, thanks so much to the person who helped me with my last question if he
reads this, I was sick for a day and I couldn't get back here, then I
couldn't find my old post to thank you!

jezzica85

Actually, there's one more question I just thought of--is there an easy way
to list the individual criteria in alphabetical order that have, say, X in
the second column?
Thanks again!

"jezzica85" wrote:

Thanks a lot, Sloth, that worked great!

"Sloth" wrote:

you can't do this with conditional formatting, because you can only have up
to three conditions (four if you include the default condition).

You could insert a column inbetween columns A and B, and insert this formula
=IF(C10,"X","")&IF(D10,"Y","")&IF(E10,"Z","" )
It would look like this

Criteria XYZ X Y Z SUM
a YZ 0 1 3 4
b XZ 1 0 5 6
c XY 7 9 0 16
d Z 0 0 2 2
e Y 0 7 0 7
f X 4 0 0 4

"jezzica85" wrote:

Hi all,
I have a sequence of five columns, they look something like this:

Criteria X Y Z SUM
a 0 1 3 4
b 1 0 5 6
c 7 9 0 16
d 0 0 2 2
e 0 7 0 7
f 4 0 0 4

Is there a way to use conditional formatting to mark the entry in the
criteria column for these conditions:
Nonzero number only in first column
Nonzero number only in second column
Nonzero number only in third column
Nonzero number only in first and second column
Nonzero number only in first and third column
Nonzero number only in second and third column

I know this is kind of a complex question, thanks in advance!

PS, thanks so much to the person who helped me with my last question if he
reads this, I was sick for a day and I couldn't get back here, then I
couldn't find my old post to thank you!

jezzica85

You can use a standard Data-Sort to alphabetize the Criteria column, and
then use Data-Filter-Autofilter to filter out everything but X's.

"jezzica85" wrote:

Actually, there's one more question I just thought of--is there an easy way
to list the individual criteria in alphabetical order that have, say, X in
the second column?
Thanks again!

"jezzica85" wrote:

Thanks a lot, Sloth, that worked great!

"Sloth" wrote:

you can't do this with conditional formatting, because you can only have up
to three conditions (four if you include the default condition).

You could insert a column inbetween columns A and B, and insert this formula
=IF(C10,"X","")&IF(D10,"Y","")&IF(E10,"Z","" )
It would look like this

Criteria XYZ X Y Z SUM
a YZ 0 1 3 4
b XZ 1 0 5 6
c XY 7 9 0 16
d Z 0 0 2 2
e Y 0 7 0 7
f X 4 0 0 4

"jezzica85" wrote:

Hi all,
I have a sequence of five columns, they look something like this:

Criteria X Y Z SUM
a 0 1 3 4
b 1 0 5 6
c 7 9 0 16
d 0 0 2 2
e 0 7 0 7
f 4 0 0 4

Is there a way to use conditional formatting to mark the entry in the
criteria column for these conditions:
Nonzero number only in first column
Nonzero number only in second column
Nonzero number only in third column
Nonzero number only in first and second column
Nonzero number only in first and third column
Nonzero number only in second and third column

I know this is kind of a complex question, thanks in advance!

PS, thanks so much to the person who helped me with my last question if he
reads this, I was sick for a day and I couldn't get back here, then I
couldn't find my old post to thank you!

jezzica85