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