MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Compound COUNTIF statements

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


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
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:


The result should return the number 3.


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



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

Hope this helps


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

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


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