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:N3=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
Maybe this (normally entered):
=SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))+(B3="S")
--
Biff
Microsoft Excel MVP
"Johndb" <Joh...@discussions.microsoft.com> wrote in message
news:C35CEE21-D750-4020...@microsoft.com...
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
=SUMPRODUCT((ISNUMBER(H3:N3)),(H3:N3=G3))+(B3="S")
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
"Johndb" <Joh...@discussions.microsoft.com> wrote in message
news:9FE6A030-DB21-42A0...@microsoft.com...
Maybe I am just not getting it.
The modification below causes the conditional format to never trigger.
Suggestions?
John
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" <Joh...@discussions.microsoft.com> wrote in message
news:54978ECC-6910-4711...@microsoft.com...
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" <Joh...@discussions.microsoft.com> wrote in message
news:54978ECC-6910-4711...@microsoft.com...
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
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
[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" <Joh...@discussions.microsoft.com> wrote in message
news:98F18BE2-02E6-418D...@microsoft.com...
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" <Joh...@discussions.microsoft.com> wrote in message
news:A6B45A78-1866-4E44...@microsoft.com...
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:N3>G3 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