[JRD]

How to count the number of cells in a column which do not contain a certain
text string?

[ExcelBanter AI]

To count the number of cells in a column that do not contain a certain text string, follow these steps:

1. Select the cell where you want to display the result of the count.
2. Type the formula
   Formula:

   =COUNTIF(range,"<"&text) 

   into the formula bar, where "range" is the range of cells you want to count and "text" is the text string you want to exclude.
3. Press Enter to calculate the result.

For example, if you want to count the number of cells in column A that do not contain the text string "apple", you would use the formula

Formula:

=COUNTIF(A:A,"<"&"apple") 


.

This formula works by using the "<" operator to exclude cells that contain the specified text string, and concatenating the text string with the "&" symbol to include cells that do not contain an exact match of the text string.

[Teethless mama]

=COUNTIF(A1:A100,"<apple")


"JRD" wrote:

How to count the number of cells in a column which do not contain a certain
text string?

[Alan Beban[_2_]]

Teethless mama wrote:
=COUNTIF(A1:A100,"<apple")


"JRD" wrote:


How to count the number of cells in a column which do not contain a certain
text string?

More responsively,

=COUNTIF(A1:A100,"<*apple*")

Alan Beban

[Harlan Grove[_2_]]

Alan Beban wrote...
....
More responsively,

=COUNTIF(A1:A100,"<*apple*")

And on the off chance the OP wants case-sensitive matching, the array
formula

=COUNT(FIND("apple",range))

[TJ Singh]

Funny thing with Excel 2003, using =countif(A1:A14,"Apple"), just doesn't seem to count at times,but changing it to "Apples" gets you a count? Is it a bug or is it about Microsoft and Apple?

[Bernie Deitrick]

Apple < Apples

Try

=countif(A1:A14,"Apple*")
or
=countif(A1:A14,"*Apple*")

HTH,
Bernie
MS Excel MVP


<TJ Singh wrote in message ...
Funny thing with Excel 2003, using =countif(A1:A14,"Apple"), just doesn't seem to count at
times,but changing it to "Apples" gets you a count? Is it a bug or is it about Microsoft and
Apple?