Combine COUNTIF & EXACT

[Guest]

Trying to find way to combine COUNTIF & EXACT with the following example,
that seems to work as is, but need to "EXACT" the same characters. thanks

=IF(OR(COUNTIF(AB9:AD9,{"os","p","x"})>0),0,1)

 

[T. Valko]

Try this:

=IF(COUNTA(AB9:AD9),--(SUMPRODUCT(--(ISNUMBER(FIND({"os";"p";"x"},AB9:AD9))))=0),"")

 

[Peo Sjoblom]

Countif is not case sensitive so you cannot use that, if you only want zero
or one as a result you can simply use


=--(SUMPRODUCT(--(EXACT(AB9:AD9,{"os","p","x"})))=0)

 

[Harlan Grove]

Try this:

=IF(COUNTA(AB9:AD9),
--(SUMPRODUCT(--(ISNUMBER(FIND({"os";"p";"x"},AB9:AD9))))=0),"")

....

Nice try, but your FIND call will look for matches anywhere in the values of
AB9:AD9, which would be similar to COUNTIF(AB9:AD9,"*"&{"os";"p";"x"}&"*"),
but that's not what the OP's formula does. OP's formula performs WHOLE
CONTENTS matching, just case-sensitive.

And the IF(COUNTA(..) bit? Where did the OP ask for that?

If all you want is 0 when none of the three cells equals "os", "p" or "x"
and 1 when one or more of these cells equal any of these text values, try
the array formula

=1-OR(EXACT(AB9:AD9,{"os";"p";"x"}))

Unfortunately, EXACT is one of those ancient, irritating functions that can
only handle array arguments when entered in array formulas. If you want to
avoid array formulas, try the longer formula

=1+SUMPRODUCT(-(SUBSTITUTE(B11,{"os";"p";"x"},"",1)=""))

 

[Guest]

or
=--(sumproduct(--(AB9:AD9=={"os","p","x"}))=0)

 

[Peo Sjoblom]

Both are actually wrong, if you replace the comma with a semicolon in the
criteria part mine will work but since the OP wanted case sensitive result
yours won't work since it will count OS, Os, oS, P and X as well


=--(SUMPRODUCT(--(EXACT(AB9:AD9,{"os";"p";"x"})))=0)



--
Regards,

Peo Sjoblom

 

[Guest]

I shouldn't have believed Excel help
under exact it says
"You can also use the double equals (==) comparison operator instead of the
EXACT function to make exact comparisons. For example, =A1==B1 returns the
same value as =EXACT(A1,B1)."

Of course when I just tried it it would not accept the double ==.
I have vauge memories of using this in the past. I currently have 2003.

 

[T. Valko]

...

Nice try, but your FIND call will look for matches anywhere in the values
of AB9:AD9, which would be similar to
COUNTIF(AB9:AD9,"*"&{"os";"p";"x"}&"*"), but that's not what the OP's
formula does. OP's formula performs WHOLE CONTENTS matching, just
case-sensitive.

I don't see my reply but it must be "there" since you're picking it apart!

Yes, I know FIND will match substrings BUT it will also match the full cell
content if the full cell content is only "os", "p", or "x".

And the IF(COUNTA(..) bit? Where did the OP ask for that?

Where did they say that they wouldn't have 3 empty cells?

 

[Harlan Grove]

Where did they say that they wouldn't have 3 empty cells?

And if they did have 3 empty cells, then none of them would be "os", "p" or
"x", so why, without reading the OP's mind (or believing you could), would
that not trigger a return value of 1?

 

[T. Valko]

--
Biff
Microsoft Excel MVP

Yes, I know FIND will match substrings BUT it will also match the full
cell content if the full cell content is only "os", "p", or "x".

What it boils down to is that I'm using the case sensitivity of FIND versus
the case sensitivity of EXACT.

 

[Harlan Grove]

What it boils down to is that I'm using the case sensitivity of FIND
versus the case sensitivity of EXACT.

Wrong.

ISNUMBER(FIND("ox","grossly inaccurate"))

returns TRUE, but EXACT("os","grossly inaccurate") returns FALSE.

The OP's formula, though case-insensitive, was full string matching. Yours
isn't. Yours could easily return false matches. Perhaps not for "os" if the
cells in question would only contain 1 or 2 chars, but if they could contain
2 chars, then why couldn't they contain, say, "np" or "xl" or "xp"? The
false matches would be more likely against "p" or "x". But I get it: you
don't want to admit you screwed up on this one.

 

[T. Valko]

Wrong.

ISNUMBER(FIND("ox","grossly inaccurate"))

returns TRUE, but EXACT("os","grossly inaccurate") returns FALSE.

The OP's formula, though case-insensitive, was full string matching. Yours
isn't. Yours could easily return false matches. Perhaps not for "os" if
the cells in question would only contain 1 or 2 chars, but if they could
contain 2 chars, then why couldn't they contain, say, "np" or "xl" or
"xp"? The false matches would be more likely against "p" or "x". But I get
it: you don't want to admit you screwed up on this one.

Search the archives for my name and the keyword disregard.

ISNUMBER(FIND("ox","grossly inaccurate")) returns TRUE

Hmmm....you sure about that?

http://img72.imageshack.us/img72/3725/findzm2.jpg

 

[T. Valko]

Search the archives for my name and the keyword disregard.


Hmmm....you sure about that?

http://img72.imageshack.us/img72/3725/findzm2.jpg

Ok, I see that your example was probably a typo and I get the point your
making...so I probably did screw up.

It won't be the last time, either, so be on the lookout!