Conditional - multiple conditions

[wmtsub]

How can I set Cell A1's background to a specific color if cells B1-H1 AND J1- W1 all have values other than blank's or zero's?

 

[Joe4]

Can it return negative numbers, or just positive ones?
If just positive numbers, try:

=AND(COUNTIF(B1:H1,">0")=7,COUNTIF(J1:W1,">0")=14)

 

[DanteAmor]

Select cell A1 and in conditional format, in formula put the following:

=SUM(IF((B1:H1<>"")*(B1:H1<>0),1,0))+SUM(IF((J1:W1<>"")*(J1:W1<>0),1,0))=21

It works for any value in the cells (positive, negative or letters)

 

[wmtsub]

Joe, yourcode gave no results at all.

 

[wmtsub]

Dante, This code did highlight the desired cell but it did so when one of the cells was blank. I need it to only hight light the cell if all the other cells have visible data in the m

 

[DanteAmor]

Dante, This code did highlight the desired cell but it did so when one of the cells was blank. I need it to only hight light the cell if all the other cells have visible data in the m

Do you have an empty cell or a blank space?

Try this

=SUM(IF((TRIM(B1:H1)<>"")*(B1:H1<>0),1,0))+SUM(IF((TRIM(J1:W1)<>"")*(J1:W1<>0),1,0))=21

 

[Joe4]

Joe, yourcode gave no results at all.

It worked on my simple example, but you really haven't told us all the possibilites of what may exist in these cells (postive numbers?, negative numbers?, text?, blanks?, spaces?, errors?).
So I have no idea of all the possibilities that you may be working with.
I would need to see what your data looks like to be able to figure out why it is not working for you (or you would need to specify which of the things I listed above may exist in these cells).

 

[wmtsub]

I am sorry i thought I was clear, the positive cell condition is anything that would be visable. The negative cell condition [un colored] could be blank spaces - deleted spaces - an actual space, that is to say anthing that makes the cell Look empty.

 

[Joe4]

It still isn't really clear, as you seem to have contradictory statements.

How can I set Cell A1's background to a specific color if cells B1-H1 AND J1- W1 all have values other than blank's or zero's?

the positive cell condition is anything that would be visable. The negative cell condition [un colored] could be blank spaces - deleted spaces - an actual space, that is to say anthing that makes the cell Look empty.

So, which one are actual zeroes included under?
In your first question, it sounds like they would be "uncolored". But in your follow-up, it sounds like they would be "colored".

It is important to be clear, complete, and concise in detailed your question. Otherwise, you may get answers that answer the question you asked, not the question you meant to ask. Remember, we know absolutely nothing about your problem, or what your data looks like. All we have to go on is what you provide to us. Many times, posting some sample data and expected results goes a lot further than just trying to explain it.

 

[DanteAmor]

I am sorry i thought I was clear, the positive cell condition is anything that would be visable. The negative cell condition [un colored] could be blank spaces - deleted spaces - an actual space, that is to say anthing that makes the cell Look empty.

Please Note
-----------------------
One thing you must keep in mind when you ask a question in a forum... the people you are asking to help you know absolutely nothing about your data, absolutely nothing about how it is laid out in the workbook, absolutely nothing about what you want done with it and absolutely nothing about how whatever it is you want done is to be presented back to you as a result... you must be very specific about describing each of these areas, in detail, and you should not assume that we will be able to "figure it out" on our own. Remember, you are asking us for help... so help us to be able to help you by providing the information we need to do so, even if that information seems "obvious" to you (remember, it is only obvious to you because of your familiarity with your data, its layout and the overall objective for it).