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


Check out our Excel Resources

Re: Compound COUNTIF statements

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

This should work

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

or

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

Hope this helps

Jacob


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

=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


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
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.