Combine COUNTIF & EXACT

N

nastech

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

T. Valko

Try this:

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

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)
 
H

Harlan Grove

T. Valko said:
Try this:

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

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(B1:D1,{"os";"p";"x"},"",1)=""))
 
P

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
 
B

bj

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

T. Valko

Harlan Grove said:
...

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.
Click to expand...

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?
Click to expand...

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

Harlan Grove

T. Valko said:
Where did they say that they wouldn't have 3 empty cells?
Click to expand...

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

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".
Click to expand...

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

Harlan Grove

T. Valko said:
What it boils down to is that I'm using the case sensitivity of FIND
versus the case sensitivity of EXACT.
Click to expand...

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

T. Valko

Harlan Grove said:
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.
Click to expand...

Search the archives for my name and the keyword disregard.
ISNUMBER(FIND("ox","grossly inaccurate")) returns TRUE
Click to expand...

Hmmm....you sure about that?

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