Combine COUNTIF & EXACT
 

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)

Combine COUNTIF & EXACT
 

Try this:

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

--
Biff
Microsoft Excel MVP


"nastech" wrote in message
...
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)

Combine COUNTIF & EXACT
 

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)


--

Regards,

Peo Sjoblom



"nastech" wrote in message
...
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)

Combine COUNTIF & EXACT
 

"T. Valko" wrote...
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?

"nastech" wrote...
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.

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

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)=""))

Combine COUNTIF & EXACT
 

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

"Peo Sjoblom" wrote:

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)


--

Regards,

Peo Sjoblom



"nastech" wrote in message
...
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)

Combine COUNTIF & EXACT
 

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







"bj" wrote in message
...
or
=--(sumproduct(--(AB9:AD9=={"os","p","x"}))=0)

"Peo Sjoblom" wrote:

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)


--

Regards,

Peo Sjoblom



"nastech" wrote in message
...
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)

Combine COUNTIF & EXACT
 

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.

"Peo Sjoblom" wrote:

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







"bj" wrote in message
...
or
=--(sumproduct(--(AB9:AD9=={"os","p","x"}))=0)

"Peo Sjoblom" wrote:

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)


--

Regards,

Peo Sjoblom



"nastech" wrote in message
...
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)

Combine COUNTIF & EXACT
 

"Harlan Grove" wrote in message
...
"T. Valko" wrote...
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.

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?

--
Biff
Microsoft Excel MVP

Combine COUNTIF & EXACT
 

"T. Valko" wrote...
....
And the IF(COUNTA(..) bit? Where did the OP ask for that?

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?

Combine COUNTIF & EXACT
 

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
"Harlan Grove" wrote in message
...
"T. Valko" wrote...
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.

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.


--
Biff
Microsoft Excel MVP

Combine COUNTIF & EXACT
 

"T. Valko" wrote...
....
"T. Valko" wrote in message
....
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.

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.

Combine COUNTIF & EXACT
 

"Harlan Grove" wrote in message
...
"T. Valko" wrote...
...
"T. Valko" wrote in message
...
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.

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

--
Biff
Microsoft Excel MVP

Combine COUNTIF & EXACT
 

"T. Valko" wrote in message
...
"Harlan Grove" wrote in message
...
"T. Valko" wrote...
...
"T. Valko" wrote in message
...
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.

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

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!

--
Biff
Microsoft Excel MVP