count all text values except a certain one
 

if i have the word 'ditto' scattered among a bunch of names (text), how do i
count every value that's not 'ditto' ? am i trying to combine "COUNTIF"
value with "NOT" criterion...?

Answer: count all text values except a certain one
 

Yes, you can use the combination of

Formula:

---

COUNTIF 


---

and

Formula:

---

NOT 


---

functions to count all text values except a certain one (in this case, 'ditto'). Here are the steps:

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

   ---

   =COUNTIF(range,"<ditto") 


   ---

   - Replace "range" with the range of cells that contains the text values you want to count (e.g. A1:A10).
   - The "<" symbol means "not equal to", so the formula will count all cells that do not contain the word "ditto".
3. Press Enter to apply the formula.

The result will be the count of all text values in the specified range that are not equal to "ditto".

count all text values except a certain one
 

One thought ..
Assuming your source data running down in col A
you could use this in say, B1:
=COUNTA(A:A)-COUNTIF(A:A,"*"&"ditto"&"*")
voila? zonk YES below
--
Max
Singapore
---
"justjill111" wrote:
if i have the word 'ditto' scattered among a bunch of names (text), how do i
count every value that's not 'ditto' ? am i trying to combine "COUNTIF"
value with "NOT" criterion...?

count all text values except a certain one
 

Try this...

=COUNTIF(A1:A10,"<ditto")

The < operator means "not equal to". I find it easier to think of it as
meaning "is not".

Note that will count empty cells.

--
Biff
Microsoft Excel MVP


"justjill111" wrote in message
...
if i have the word 'ditto' scattered among a bunch of names (text), how do
i
count every value that's not 'ditto' ? am i trying to combine "COUNTIF"
value with "NOT" criterion...?

count all text values except a certain one
 

sorry but it returned the entire count. let me be more specific.
the names are contained E7:E98 the column looks something like this:

jane
max
ditto
ditto
ditto
laurie
kelly
dittto

does this help?

"Max" wrote:

One thought ..
Assuming your source data running down in col A
you could use this in say, B1:
=COUNTA(A:A)-COUNTIF(A:A,"*"&"ditto"&"*")
voila? zonk YES below
--
Max
Singapore
---

count all text values except a certain one
 

in other works, for the below example, how do i get a count of 4 instead of 8?

"justjill111" wrote:

sorry but it returned the entire count. let me be more specific.
the names are contained E7:E98 the column looks something like this:

jane
max
ditto
ditto
ditto
laurie
kelly
ditto

does this help?

"Max" wrote:

One thought ..
Assuming your source data running down in col A
you could use this in say, B1:
=COUNTA(A:A)-COUNTIF(A:A,"*"&"ditto"&"*")
voila? zonk YES below
--
Max
Singapore
---

count all text values except a certain one
 

sorry max, your formula was perfect, i just didn't realize it in time to
avoid making a fool of myself ~!~

"justjill111" wrote:

in other works, for the below example, how do i get a count of 4 instead of 8?

"justjill111" wrote:

sorry but it returned the entire count. let me be more specific.
the names are contained E7:E98 the column looks something like this:

jane
max
ditto
ditto
ditto
laurie
kelly
ditto

does this help?

"Max" wrote:

One thought ..
Assuming your source data running down in col A
you could use this in say, B1:
=COUNTA(A:A)-COUNTIF(A:A,"*"&"ditto"&"*")
voila? zonk YES below
--
Max
Singapore
---