[widgeboy]

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?

[Yorkshire Guy]

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

[bomb #21]

Originally Posted by widgeboy

Can I use COUNTIF?

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"))

[widgeboy]

Thats great - thanks very much!! The second line is the best for me here.
Cheers.

[carl schmitt]

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.

[jimr381]

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.