Countif with 2 criteria
 

I need to count if there's the number of ocurrences containing 100/101 but
when it does not have the word "cancel".

May be something like that:

COUNTIF(A1:A10,"*100/101*") but <"cancel"

Please help.

Countif with 2 criteria
 

One way:
=SUMPRODUCT((ISNUMBER(SEARCH("100/101",A1:A10))*ISERROR(SEARCH("cancel",A1:A10))))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"mldancing" wrote:
I need to count if there's the number of ocurrences containing 100/101 but
when it does not have the word "cancel".

May be something like that:

COUNTIF(A1:A10,"*100/101*") but <"cancel"

Please help.

Countif with 2 criteria
 

=SUM(IF(ISNUMBER(SEARCH("100/101",A1:A100))*(NOT(ISNUMBER(SEARCH("cancel",A1:A1 00)))),1))

ctrl+shift+enter, not just enter

"mldancing" wrote:

I need to count if there's the number of ocurrences containing 100/101 but
when it does not have the word "cancel".

May be something like that:

COUNTIF(A1:A10,"*100/101*") but <"cancel"

Please help.

Countif with 2 criteria
 

Both way works ... Thank you, Max and teethless mama!



"Teethless mama" wrote:

=SUM(IF(ISNUMBER(SEARCH("100/101",A1:A100))*(NOT(ISNUMBER(SEARCH("cancel",A1:A1 00)))),1))

ctrl+shift+enter, not just enter

"mldancing" wrote:

I need to count if there's the number of ocurrences containing 100/101 but
when it does not have the word "cancel".

May be something like that:

COUNTIF(A1:A10,"*100/101*") but <"cancel"

Please help.

Countif with 2 criteria
 

One question:

when do you decide that you have to use ctrl+shift+enter?



"Teethless mama" wrote:

=SUM(IF(ISNUMBER(SEARCH("100/101",A1:A100))*(NOT(ISNUMBER(SEARCH("cancel",A1:A1 00)))),1))

ctrl+shift+enter, not just enter

"mldancing" wrote:

I need to count if there's the number of ocurrences containing 100/101 but
when it does not have the word "cancel".

May be something like that:

COUNTIF(A1:A10,"*100/101*") but <"cancel"

Please help.