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 dont 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