What version of Excel are you using?

There is a limit to the number of CF conditions you can apply depending on
the version of Excel. In versions prior to Excel 2007 the limit is 3
conditions. In Excel 2007 there is no number limit, the limit is system
resources.

If you're using a version of Excel prior to Excel 2007 then you'll have to
use a macro to cover more than 3 conditions.

--
Biff
Microsoft Excel MVP


"Johndb" wrote in message
...
T,

For example: I would like to CF cell C3 based on the contents of cells
H3:N3 for cells C3:C37.
If B3 = "x" light green
If any cell in H3:N3 = "N" or "L" red
If any cell in H3:N3 G3 pink
If any cell in H3:N3 = G3 AND B3 = "S" yellow
If any cell in H3:N3 = G3 AND B3 < "S" gray

H3:N3 can contain Blanks, Text, or Numbers.
G3 can contain Text or Numbers.

Hope this helps.

Respectfully,

John


Additionally:

David,

I am only an intermediate user, I hope the below helps you help me.

Put =B3="S" in a cell. What value do you get? True
Put =SUMPRODUCT((in a cell. What value do you get? H3:N3
???)),(H3:N3=G3))
in a cell.
What value do you get? #VALUE!
Put =SUMPRODUCT((ISNUMBER(H3:N3)),(H3:N3=G3)) in a cell. What value do you
get? 0
Put =SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))*(B3="S") in a cell. What
value do you get? 1
Put =SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))*(B3="S") in a cell. What
value do you get? Same as above, 1.

If you still don't understand, you may need to go a step further and look
at
=ISNUMBER(H3) to =ISNUMBER(N3), and at =H3=$G3 to =N3=$G3
I don't understand this question/instruction.


Respectfully,

John




"T. Valko" wrote:

Maybe I am just not getting it.

I think you need to give us some more detail about what you're trying to
do.

You said this is for CF. What cell(s) are you wanting to format? Based on
what condition(s)?

OK, we've determined that B3=S is a separate condition.

Does H3:N3 ever contain TEXT? Might there be formula blanks in the range?
What's in G3?

These are the kinds of details we need to know.

--
Biff
Microsoft Excel MVP


"Johndb" wrote in message
...
Thanks David.

Maybe I am just not getting it.

The modification below causes the conditional format to never trigger.
Suggestions?

John



"David Biddulph" wrote:

Well of course it triggers the conditional format if B3="S" as you've
added
+(B3="S") to the SUMPRODUCT. That addition is effectively an OR
function.
If you wanted an AND function, try *(B3="S") instead of +(B3="S")
--
David Biddulph

"Johndb" wrote in message
...
Friends,

These formulas:

=SUMPRODUCT((ISNUMBER(H3:N3)),(H3:N3=G3))+(B3="S")
=SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))+(B3="S")

are still triggering the conditional format even though H3:N3
does
not
equal G3.

Example: When G3=2 and H3:N3 contains no matching 2 and B3="S" it is
still
triggering the conditional format. This also occurs when H3:N3
contains
zeroes and blanks. It should only trigger the conditional format
when
there
is a matching value between G3 and any field within H3:N3.

Thanks for everyone's help so far, any further ideas?

Thanks for the effort,

John

"Eduardo" wrote:

Hi try

=SUMPRODUCT((ISNUMBER(H3:N3)),(H3:N3=G3))+(B3="S")


"Johndb" wrote:

T,

This doesn't seem to work. I am using the formula to trigger a
conditional
format (amongst other conditional formats) and now

=SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))+(B3="S")

(h3:n3=g3)
is not
being evaluated before triggering the conditional format.

Any other ideas, or am I screwing it up?

John
"T. Valko" wrote:

Hmmm...

Maybe this (normally entered):

=SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))+(B3="S")


--
Biff
Microsoft Excel MVP


"Johndb" wrote in message
...
Need help.

I need to add an AND command to the following formula and
can't
seem to
figure it out.

=SUM(IF(ISNUMBER(H3:N3),IF(ISNUMBER(H3:N3),IF(H3:N 3=G3,1,0))))

and B3 = "S"

The formula also needs to check if b3 is = to "S"

Any assistance would be geatly appreciated.

Warmest Regards,

John