software

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
lt;Formatgt;lt;Conditional Formattinggt;lt;Condition 1gt;
Cell Formula is: =COUNTIF(B22,0)gt;0
Click the [Format...] button and set the format you want

Does that help?

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

XL2002, WinXP-Proquot;jezzica85quot; wrote:

gt; Hi all,
gt; I have a sequence of five columns, they look something like this:
gt;
gt; Criteria X Y Z SUM
gt; a 0 1 3 4
gt; b 1 0 5 6
gt; c 7 9 0 16
gt; d 0 0 2 2
gt; e 0 7 0 7
gt; f 4 0 0 4
gt;
gt; Is there a way to use conditional formatting to mark the entry in the
gt; criteria column for these conditions:
gt; Nonzero number only in first column
gt; Nonzero number only in second column
gt; Nonzero number only in third column
gt; Nonzero number only in first and second column
gt; Nonzero number only in first and third column
gt; Nonzero number only in second and third column
gt;
gt; I know this is kind of a complex question, thanks in advance!
gt;
gt; PS, thanks so much to the person who helped me with my last question if he
gt; reads this, I was sick for a day and I couldn't get back here, then I
gt; couldn't find my old post to thank you!
gt;
gt; 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(C1lt;gt;0,quot;Xquot;,quot;quot;)amp;IF(D1lt;gt;0,quot;Yquot;,quot;quot;)amp;IF(E1lt;gt;0,quot;Zquot;,quot;quot; )
It would look like this

CriteriaXYZXYZSUM
aYZ0134
bXZ1056
cXY79016
dZ0022
eY0707
fX4004

quot;jezzica85quot; wrote:

gt; Hi all,
gt; I have a sequence of five columns, they look something like this:
gt;
gt; Criteria X Y Z SUM
gt; a 0 1 3 4
gt; b 1 0 5 6
gt; c 7 9 0 16
gt; d 0 0 2 2
gt; e 0 7 0 7
gt; f 4 0 0 4
gt;
gt; Is there a way to use conditional formatting to mark the entry in the
gt; criteria column for these conditions:
gt; Nonzero number only in first column
gt; Nonzero number only in second column
gt; Nonzero number only in third column
gt; Nonzero number only in first and second column
gt; Nonzero number only in first and third column
gt; Nonzero number only in second and third column
gt;
gt; I know this is kind of a complex question, thanks in advance!
gt;
gt; PS, thanks so much to the person who helped me with my last question if he
gt; reads this, I was sick for a day and I couldn't get back here, then I
gt; couldn't find my old post to thank you!
gt;
gt; 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 gt;0 after the parentheses before, what does that do?
Thanks!

quot;Ron Coderrequot; wrote:

gt; Unless I'm missing something, wouldn't this work?:
gt;
gt; Select A2
gt; lt;Formatgt;lt;Conditional Formattinggt;lt;Condition 1gt;
gt; Cell Formula is: =COUNTIF(B22,0)gt;0
gt; Click the [Format...] button and set the format you want
gt;
gt; Does that help?
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;jezzica85quot; wrote:
gt;
gt; gt; Hi all,
gt; gt; I have a sequence of five columns, they look something like this:
gt; gt;
gt; gt; Criteria X Y Z SUM
gt; gt; a 0 1 3 4
gt; gt; b 1 0 5 6
gt; gt; c 7 9 0 16
gt; gt; d 0 0 2 2
gt; gt; e 0 7 0 7
gt; gt; f 4 0 0 4
gt; gt;
gt; gt; Is there a way to use conditional formatting to mark the entry in the
gt; gt; criteria column for these conditions:
gt; gt; Nonzero number only in first column
gt; gt; Nonzero number only in second column
gt; gt; Nonzero number only in third column
gt; gt; Nonzero number only in first and second column
gt; gt; Nonzero number only in first and third column
gt; gt; Nonzero number only in second and third column
gt; gt;
gt; gt; I know this is kind of a complex question, thanks in advance!
gt; gt;
gt; gt; PS, thanks so much to the person who helped me with my last question if he
gt; gt; reads this, I was sick for a day and I couldn't get back here, then I
gt; gt; couldn't find my old post to thank you!
gt; gt;
gt; gt; 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.

quot;jezzica85quot; wrote:

gt; I'd like to have a different format for each of those conditions I put down,
gt; not the same one for each. Would this formula do that? I've never seen
gt; something like that gt;0 after the parentheses before, what does that do?
gt; Thanks!
gt;
gt; quot;Ron Coderrequot; wrote:
gt;
gt; gt; Unless I'm missing something, wouldn't this work?:
gt; gt;
gt; gt; Select A2
gt; gt; lt;Formatgt;lt;Conditional Formattinggt;lt;Condition 1gt;
gt; gt; Cell Formula is: =COUNTIF(B22,0)gt;0
gt; gt; Click the [Format...] button and set the format you want
gt; gt;
gt; gt; Does that help?
gt; gt;
gt; gt; ***********
gt; gt; Regards,
gt; gt; Ron
gt; gt;
gt; gt; XL2002, WinXP-Pro
gt; gt;
gt; gt;
gt; gt; quot;jezzica85quot; wrote:
gt; gt;
gt; gt; gt; Hi all,
gt; gt; gt; I have a sequence of five columns, they look something like this:
gt; gt; gt;
gt; gt; gt; Criteria X Y Z SUM
gt; gt; gt; a 0 1 3 4
gt; gt; gt; b 1 0 5 6
gt; gt; gt; c 7 9 0 16
gt; gt; gt; d 0 0 2 2
gt; gt; gt; e 0 7 0 7
gt; gt; gt; f 4 0 0 4
gt; gt; gt;
gt; gt; gt; Is there a way to use conditional formatting to mark the entry in the
gt; gt; gt; criteria column for these conditions:
gt; gt; gt; Nonzero number only in first column
gt; gt; gt; Nonzero number only in second column
gt; gt; gt; Nonzero number only in third column
gt; gt; gt; Nonzero number only in first and second column
gt; gt; gt; Nonzero number only in first and third column
gt; gt; gt; Nonzero number only in second and third column
gt; gt; gt;
gt; gt; gt; I know this is kind of a complex question, thanks in advance!
gt; gt; gt;
gt; gt; gt; PS, thanks so much to the person who helped me with my last question if he
gt; gt; gt; reads this, I was sick for a day and I couldn't get back here, then I
gt; gt; gt; couldn't find my old post to thank you!
gt; gt; gt;
gt; gt; gt; jezzica85

Thanks a lot, Sloth, that worked great!

quot;Slothquot; wrote:

gt; you can't do this with conditional formatting, because you can only have up
gt; to three conditions (four if you include the default condition).
gt;
gt; You could insert a column inbetween columns A and B, and insert this formula
gt; =IF(C1lt;gt;0,quot;Xquot;,quot;quot;)amp;IF(D1lt;gt;0,quot;Yquot;,quot;quot;)amp;IF(E1lt;gt;0,quot;Zquot;,quot;quot; )
gt; It would look like this
gt;
gt; CriteriaXYZXYZSUM
gt; aYZ0134
gt; bXZ1056
gt; cXY79016
gt; dZ0022
gt; eY0707
gt; fX4004
gt;
gt; quot;jezzica85quot; wrote:
gt;
gt; gt; Hi all,
gt; gt; I have a sequence of five columns, they look something like this:
gt; gt;
gt; gt; Criteria X Y Z SUM
gt; gt; a 0 1 3 4
gt; gt; b 1 0 5 6
gt; gt; c 7 9 0 16
gt; gt; d 0 0 2 2
gt; gt; e 0 7 0 7
gt; gt; f 4 0 0 4
gt; gt;
gt; gt; Is there a way to use conditional formatting to mark the entry in the
gt; gt; criteria column for these conditions:
gt; gt; Nonzero number only in first column
gt; gt; Nonzero number only in second column
gt; gt; Nonzero number only in third column
gt; gt; Nonzero number only in first and second column
gt; gt; Nonzero number only in first and third column
gt; gt; Nonzero number only in second and third column
gt; gt;
gt; gt; I know this is kind of a complex question, thanks in advance!
gt; gt;
gt; gt; PS, thanks so much to the person who helped me with my last question if he
gt; gt; reads this, I was sick for a day and I couldn't get back here, then I
gt; gt; couldn't find my old post to thank you!
gt; gt;
gt; gt; 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-Proquot;Slothquot; wrote:

gt; No offence Ron, but this won't work. the formula counts the number of times
gt; 0 appears in the three columns, and compares that count to 0. If the count
gt; is greater than zero, then the formula results in TRUE and triggers the
gt; conditional format. So basically you will get the same format for all cases
gt; except when all three columns are non-zero.
gt;
gt; quot;jezzica85quot; wrote:
gt;
gt; gt; I'd like to have a different format for each of those conditions I put down,
gt; gt; not the same one for each. Would this formula do that? I've never seen
gt; gt; something like that gt;0 after the parentheses before, what does that do?
gt; gt; Thanks!
gt; gt;
gt; gt; quot;Ron Coderrequot; wrote:
gt; gt;
gt; gt; gt; Unless I'm missing something, wouldn't this work?:
gt; gt; gt;
gt; gt; gt; Select A2
gt; gt; gt; lt;Formatgt;lt;Conditional Formattinggt;lt;Condition 1gt;
gt; gt; gt; Cell Formula is: =COUNTIF(B22,0)gt;0
gt; gt; gt; Click the [Format...] button and set the format you want
gt; gt; gt;
gt; gt; gt; Does that help?
gt; gt; gt;
gt; gt; gt; ***********
gt; gt; gt; Regards,
gt; gt; gt; Ron
gt; gt; gt;
gt; gt; gt; XL2002, WinXP-Pro
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;jezzica85quot; wrote:
gt; gt; gt;
gt; gt; gt; gt; Hi all,
gt; gt; gt; gt; I have a sequence of five columns, they look something like this:
gt; gt; gt; gt;
gt; gt; gt; gt; Criteria X Y Z SUM
gt; gt; gt; gt; a 0 1 3 4
gt; gt; gt; gt; b 1 0 5 6
gt; gt; gt; gt; c 7 9 0 16
gt; gt; gt; gt; d 0 0 2 2
gt; gt; gt; gt; e 0 7 0 7
gt; gt; gt; gt; f 4 0 0 4
gt; gt; gt; gt;
gt; gt; gt; gt; Is there a way to use conditional formatting to mark the entry in the
gt; gt; gt; gt; criteria column for these conditions:
gt; gt; gt; gt; Nonzero number only in first column
gt; gt; gt; gt; Nonzero number only in second column
gt; gt; gt; gt; Nonzero number only in third column
gt; gt; gt; gt; Nonzero number only in first and second column
gt; gt; gt; gt; Nonzero number only in first and third column
gt; gt; gt; gt; Nonzero number only in second and third column
gt; gt; gt; gt;
gt; gt; gt; gt; I know this is kind of a complex question, thanks in advance!
gt; gt; gt; gt;
gt; gt; gt; gt; PS, thanks so much to the person who helped me with my last question if he
gt; gt; gt; gt; reads this, I was sick for a day and I couldn't get back here, then I
gt; gt; gt; gt; couldn't find my old post to thank you!
gt; gt; gt; gt;
gt; gt; gt; gt; 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!

quot;jezzica85quot; wrote:

gt; Thanks a lot, Sloth, that worked great!
gt;
gt; quot;Slothquot; wrote:
gt;
gt; gt; you can't do this with conditional formatting, because you can only have up
gt; gt; to three conditions (four if you include the default condition).
gt; gt;
gt; gt; You could insert a column inbetween columns A and B, and insert this formula
gt; gt; =IF(C1lt;gt;0,quot;Xquot;,quot;quot;)amp;IF(D1lt;gt;0,quot;Yquot;,quot;quot;)amp;IF(E1lt;gt;0,quot;Zquot;,quot;quot; )
gt; gt; It would look like this
gt; gt;
gt; gt; CriteriaXYZXYZSUM
gt; gt; aYZ0134
gt; gt; bXZ1056
gt; gt; cXY79016
gt; gt; dZ0022
gt; gt; eY0707
gt; gt; fX4004
gt; gt;
gt; gt; quot;jezzica85quot; wrote:
gt; gt;
gt; gt; gt; Hi all,
gt; gt; gt; I have a sequence of five columns, they look something like this:
gt; gt; gt;
gt; gt; gt; Criteria X Y Z SUM
gt; gt; gt; a 0 1 3 4
gt; gt; gt; b 1 0 5 6
gt; gt; gt; c 7 9 0 16
gt; gt; gt; d 0 0 2 2
gt; gt; gt; e 0 7 0 7
gt; gt; gt; f 4 0 0 4
gt; gt; gt;
gt; gt; gt; Is there a way to use conditional formatting to mark the entry in the
gt; gt; gt; criteria column for these conditions:
gt; gt; gt; Nonzero number only in first column
gt; gt; gt; Nonzero number only in second column
gt; gt; gt; Nonzero number only in third column
gt; gt; gt; Nonzero number only in first and second column
gt; gt; gt; Nonzero number only in first and third column
gt; gt; gt; Nonzero number only in second and third column
gt; gt; gt;
gt; gt; gt; I know this is kind of a complex question, thanks in advance!
gt; gt; gt;
gt; gt; gt; PS, thanks so much to the person who helped me with my last question if he
gt; gt; gt; reads this, I was sick for a day and I couldn't get back here, then I
gt; gt; gt; couldn't find my old post to thank you!
gt; gt; gt;
gt; gt; gt; jezzica85

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

quot;jezzica85quot; wrote:

gt; Actually, there's one more question I just thought of--is there an easy way
gt; to list the individual criteria in alphabetical order that have, say, X in
gt; the second column?
gt; Thanks again!
gt;
gt; quot;jezzica85quot; wrote:
gt;
gt; gt; Thanks a lot, Sloth, that worked great!
gt; gt;
gt; gt; quot;Slothquot; wrote:
gt; gt;
gt; gt; gt; you can't do this with conditional formatting, because you can only have up
gt; gt; gt; to three conditions (four if you include the default condition).
gt; gt; gt;
gt; gt; gt; You could insert a column inbetween columns A and B, and insert this formula
gt; gt; gt; =IF(C1lt;gt;0,quot;Xquot;,quot;quot;)amp;IF(D1lt;gt;0,quot;Yquot;,quot;quot;)amp;IF(E1lt;gt;0,quot;Zquot;,quot;quot; )
gt; gt; gt; It would look like this
gt; gt; gt;
gt; gt; gt; CriteriaXYZXYZSUM
gt; gt; gt; aYZ0134
gt; gt; gt; bXZ1056
gt; gt; gt; cXY79016
gt; gt; gt; dZ0022
gt; gt; gt; eY0707
gt; gt; gt; fX4004
gt; gt; gt;
gt; gt; gt; quot;jezzica85quot; wrote:
gt; gt; gt;
gt; gt; gt; gt; Hi all,
gt; gt; gt; gt; I have a sequence of five columns, they look something like this:
gt; gt; gt; gt;
gt; gt; gt; gt; Criteria X Y Z SUM
gt; gt; gt; gt; a 0 1 3 4
gt; gt; gt; gt; b 1 0 5 6
gt; gt; gt; gt; c 7 9 0 16
gt; gt; gt; gt; d 0 0 2 2
gt; gt; gt; gt; e 0 7 0 7
gt; gt; gt; gt; f 4 0 0 4
gt; gt; gt; gt;
gt; gt; gt; gt; Is there a way to use conditional formatting to mark the entry in the
gt; gt; gt; gt; criteria column for these conditions:
gt; gt; gt; gt; Nonzero number only in first column
gt; gt; gt; gt; Nonzero number only in second column
gt; gt; gt; gt; Nonzero number only in third column
gt; gt; gt; gt; Nonzero number only in first and second column
gt; gt; gt; gt; Nonzero number only in first and third column
gt; gt; gt; gt; Nonzero number only in second and third column
gt; gt; gt; gt;
gt; gt; gt; gt; I know this is kind of a complex question, thanks in advance!
gt; gt; gt; gt;
gt; gt; gt; gt; PS, thanks so much to the person who helped me with my last question if he
gt; gt; gt; gt; reads this, I was sick for a day and I couldn't get back here, then I
gt; gt; gt; gt; couldn't find my old post to thank you!
gt; gt; gt; gt;
gt; gt; gt; gt; jezzica85