# Solved: A quick COUNTIF solution

Discussion in 'Archive: Business Applications' started by widgeboy, Aug 2, 2006.

Not open for further replies.

Joined:
Jul 17, 2006
Messages:
43
Hello tech wizards.

I have a problem that I am sure is simple to fix. Lets say I have a sheet with 2 columns, and 100 rows. Each of the cells are populated with the name of a fruit. What is the best way of counting the number of times that a certain situation occurs. For example, how many times in that data set, is there an "apple" in column A, and "pear" in column B. Can I use COUNTIF?

2. ### Yorkshire Guy

Joined:
Dec 9, 2003
Messages:
563
Hi Widgeboy,

Where do you want the counts, next to every occurence of say "Apple", or just once at the bottom of the list.

For the latter:
in a cell enter =COUNTIF(A1:A100,"Apples")
or =COUNTIF(B1:B5,"Pear")

For the former, entering =COUNTIF(A1:A100,A1) will count all the entries in col A that match what is in A1; copy this down the column.

lol
Hew

3. ### bomb #21

Joined:
Jul 1, 2005
Messages:
8,546
Doubtful. There are various "Summing and Counting Using Multiple Criteria" examples at http://www.j-walk.com/ss/excel/tips/tip74.htm. The relevant one would be "Count of Sales where Month=Jan AND Region=North", so you'd end up with something like:

{=SUM((A1:A100="Apple")*(B1:B100="Pear"))}

(NB: you don't type the curly brackets, they're created when you use CTRL+Shift+Enter -- instead of plain Enter -- to enter the formula ; it's called an array formula).

OTOH, many folks these days use SUMPRODUCT to avoid using an array formula. Which would be something like:

=SUMPRODUCT((A1:A100="Apple")*(B1:B100="Pear"))

Joined:
Jul 17, 2006
Messages:
43
Thats great - thanks very much!! The second line is the best for me here.
Cheers.

5. ### carl schmitt

Joined:
Mar 21, 2008
Messages:
1
I tried formulas in # 21. Neither sumproduct nor sum yield a count- I get o (zero) each time

I am dealing with 2 ranges and trying to count the number of hits in each, given the other.

For example, trying to count the number that hve a 1-Large City in range AD2..Ad4629 while also having a Yes in range Bo2..bo4629.

6. ### jimr381

Joined:
Jul 20, 2007
Messages:
4,189
First Name:
Jim
Welcome to the forum Carl. Have you looked into a pivottable before? It is usually a good idea to start your own thread as well to get more exposure.

As Seen On