1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: A quick COUNTIF solution

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

Thread Status:
Not open for further replies.
Advertisement
  1. widgeboy

    widgeboy Thread Starter

    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

    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

    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"))
     
  4. widgeboy

    widgeboy Thread Starter

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

    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

    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. ;)
     
  7. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/488712

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice