countif adding AND

  • Thread starter Thread starter Johndb
  • Start date Start date
J

Johndb

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: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
 
Hmmm...

Maybe this (normally entered):

=SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))+(B3="S")
 
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
 
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
 
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")
 
Thanks David.

Maybe I am just not getting it.

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

John
 
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.
 
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
 
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
 
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
 
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.]
 
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.
 
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: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


David Biddulph said:
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 said:
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
Click to expand...
Click to expand...
 
Back
Top