Archive of Mr Excel Message Board

Back to Forms in Excel VBA archive index
Back to archive home

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.

Check out our Excel Resources

Re: Compound COUNTIF statements

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


Re: Compound COUNTIF statements

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


This archive is from the original message board at
All contents © 1998-2004
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.