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

  • Thread starter Thread starter DartGuru
  • Start date Start date
D

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.
 
try this

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

use ctrl + shift + enter
Click to expand...

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

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

--ron
 
Hi
Try
=SUMPRODUCT(--(ISNUMBER(SEARCH("Before",A1:A6))),
--(NOT(ISNUMBER(SEARCH("After",A1:A6)))))
 
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.
 
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
 
Back
Top