David,

I've got the original problem fixed now I think, thank you. However, now
two out the five conditional formats I wrote aren't aren't working right. I
believe this is my fault, and they just need rewriting.

Using the base of, which works:

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

How would I write:

=SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))*(B3="S")
H3:N3="N" OR "L"
with no reference to B3 at all.

=SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))*(B3="S")
H3:N3G3 with no
reference to B3 at all.

You've been of great help and patience, help me get the last mile.

Thanks in advance,

John


"David Biddulph" wrote:

If =SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))*(B3="S") and
=SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))*(B3="S") each give 1, and if
those formulae are what you used in your conditional formatting test, it
should have triggered the conditional formatting.
Are you sure that you used "Formula Is" in CF, not "Cell Value Is"?
Are you sure that you did specify the format you want to use in the CF
condition?

[Sorry, the =SUMPRODUCT((in a cell. What value do you get? H3:N3
???)),(H3:N3=G3)) was a copy & paste hiccup, but you've tested enough to
see that the full condition is working.]
--
David Biddulph

"Johndb" wrote in message
...
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


Additionally,

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



"David Biddulph" wrote:

I would merely make the usual suggestion for diagnosing problems in a
formula. Break it up into manageable chunks and see what values you get.

Put =B3="S" in a cell. What value do you get?
Put =SUMPRODUCT((in a cell. What value do you get?)),(H3:N3=G3)) in a
cell.
What value do you get?
Put =SUMPRODUCT((ISNUMBER(H3:N3)),(H3:N3=G3)) in a cell. What value do
you
get?
Put =SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))*(B3="S") in a cell.
What
value do you get?
Put =SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))*(B3="S") in a cell.
What
value do you get?

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
--
David Biddulph


"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