Compound COUNTIF statements

Posted by Demetrius Tickles on December 27, 2001 4:19 PM

Hello,

I am trying to get the COUNTIF function to look at a column and look at the different text values in it then give me a total for all of them.

Sample of the column B
___B_____
1 East
2 West
3 North
4 West
5 South
6 South
7 North
8 East
9 West

If I wanted to get a count on the total number of items with the name "West", I could use the COUNTIF function as follows and get the number 3 returned:

=COUNTIF(B1:B9,"West")

The result should return the number 3.

QUESTION

If I wanted to count the other items (East, South and North) also, how can the COUNTIF statement be compounded to return the number 9 as that is the total number of them all?

Any help you could offer would be greatly appreciated.

Posted by Jacob on December 27, 2001 4:47 PM

This should work

=Sum(COUNTIF(B1:B9,"West"),COUNTIF(B1:B9,"South"),COUNTIF(B1:B9,"North"))

=COUNTIF(B1:B9,"West") + COUNTIF(B1:B9,"South") + COUNTIF(B1:B9,"North")

Hope this helps

Jacob

Posted by Chris D on December 27, 2001 5:32 PM

Not strictly what you asked in terms of compounding countif, but

=counta(b1:b9)-countif(b1:b9,"west")

would count everything and deduct the count of "west", in case any other values were added that weren't "west" (ie north-east or south-east etc) you wouldn't need to keep updating your countif formulae

HTH
Chris