1. Countif (XP)

Is it possible to use a countif such as =countif(range not equal to "a123","b246","c357")? Range is to represent the column and cells for this example. What I am trying to do is find a method of counting various text at one time without having to use separate formulas for each example of text I am looking for.

2. Re: Countif (XP)

Try
<code>
=COUNTA(range)-SUM(COUNTIF(range,{"a123","b246","c357"})</code>

3. Re: Countif (XP)

To expand on Hans' reply. If you want to be able to check one range against another range of cells you can use the following array formula (confirm with Ctrl+Shift+Enter)

{=COUNTA(range)-SUM(COUNTIF(range,range2))}

Where range2 is a range of cells that contain the "a123","b246","c357"

5. Re: Countif (XP)

Assume the range of data located at : A1:A10, try to use the following formula :

=SUM(COUNTIF(A1:A10,{"<>","a123","b246","c357"})*{ 1,-1,-1,-1})

Regards
Bosco

6. Re: Countif (XP)

Would you mind explaining the point of *[1,-1,-1,-1} Thanks.

7. Re: Countif (XP)

COUNTIF(A1:A10,"<>") returns the number of non-blank cells in A1:A10 (just like COUNTA). This value is multiplied with 1.
COUNTIF(A1:A10,"a123") returns the number of cells equal to "a123". This value is multiplied with -1, so in the SUM it is in fact subtracted.
Similar for the other two.
So

=SUM(COUNTIF(A1:A10,{"<>","a123","b246","c357"})*{ 1,-1,-1,-1})

is equivalent to

=COUNTIF(A1:A10,"<>")-COUNTIF(A1:A10,"a123")-COUNTIF(A1:A10,"b246")-COUNTIF(A1:A10,"c357")

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•