Using COUNTIF to count explicit text that DOES NOT include other text

[DartGuru]

I have a column of text strings. The strings may contain "Before",
"Before Before", "Before After", "After", "After After" and any
possible mis-spelling of the above and of course blanks.

I want to count the number of cells that contain the word "Before" and
do not contain the word "After".

I tried to be clever with COUNTIF as follows:-

=COUNTIF(COUNTIF(A:A,"*Before*"),"<>*After*")

and I've tried:-

=COUNTIF(A:A,AND("*Before*",NOT("*After*")))

The first one wouldn't compute as a formula, the latter just returned
zero (even though I knew the answer was 2).

I know I could calculate a separate column with 0 or 1 using FIND and
SEARCH, but I was trying to avoid using a separate column.

Any suggestions gratefully received.

 

[muddan madhu]

try this

=SUM((MID(A1:A100,COLUMN(1:1),6)="before")*1)

use ctrl + shift + enter

 

[DartGuru]

try this

=SUM((MID(A1:A100,COLUMN(1:1),6)="before")*1)

use ctrl + shift + enter

With the following list in column A (using "Bob" to prove a
misspelling):-
Before
Before Before
Before After
After
Before After
Bob

I would expect it to return 2, for the two cells that contain "Before"
and not "After". I get 5, as it seems to count all the "Before"s.

 

[Ron Rosenfeld]

I have a column of text strings. The strings may contain "Before",
"Before Before", "Before After", "After", "After After" and any
possible mis-spelling of the above and of course blanks.

I want to count the number of cells that contain the word "Before" and
do not contain the word "After".

I tried to be clever with COUNTIF as follows:-

=COUNTIF(COUNTIF(A:A,"*Before*"),"<>*After*")

and I've tried:-

=COUNTIF(A:A,AND("*Before*",NOT("*After*")))

The first one wouldn't compute as a formula, the latter just returned
zero (even though I knew the answer was 2).

I know I could calculate a separate column with 0 or 1 using FIND and
SEARCH, but I was trying to avoid using a separate column.

Any suggestions gratefully received.

=SUMPRODUCT(ISNUMBER(SEARCH("before",A1:A6))*ISERR(SEARCH("after",A1:A6)))

--ron

 

[Roger Govier]

Hi
Try
=SUMPRODUCT(--(ISNUMBER(SEARCH("Before",A1:A6))),
--(NOT(ISNUMBER(SEARCH("After",A1:A6)))))

 

[DartGuru]

=SUMPRODUCT(ISNUMBER(SEARCH("before",A1:A6))*ISERR(SEARCH("after",A1:A6)))

--ron

I think that just about gives me what I need, thanks.

 

[Ron Rosenfeld]

I think that just about gives me what I need, thanks.

You're welcome. Let me know if it needs any tweaking.
--ron

 

[ShaneDevenshire]

Hi,

Here is a slightly shorter formula and a second modification:

=SUMPRODUCT(NOT(ISERR(FIND("Before",A1:A6)))*ISERR(FIND("After",A1:A6)))

If you put Before in cell B1 and After in B2 then

=SUMPRODUCT(NOT(ISERR(FIND(B1,A1:A6)))*ISERR(FIND(B2,A1:A6)))


If any of these posts work for you please check the Yes button.

 

[Peo Sjoblom]

While it is shorter in counting characters it has more function calls and it
is using
the case sensitive function FIND vs. Ron's SEARCH which means it won't find
"before" or "after"

--


Regards,


Peo Sjoblom