CountIf Function

August 15th, 2004 | Categories: Formulas | Tags:
-->

The CountIf function is perfect for counting the number of times an instance of something occurs in a range.

The syntax for the CountIf function is:

=Countif(range, criteria)

How do we use this?

1. In a blank cell type =CountIf(
2. Click and drag the range to look in (this automatically fills your formula)
3. Type, (comma)
4. Enter the criteria and hit enter.

For example:
To count numbers in a range greater than 100 your criteria is “>100″ or
=CountIf(A1:A10,”>100″)

To count the total times a word appears in a range your criteria is “yourwordhere” or
=CountIf(A1:A10,”yourwordhere”)

Can't get the tutorial to work for you? Need help with your code?
Get answers right away at our AE Excel Support Forums!
Leave a comment | Trackback
  1. Topaz
    July 23rd, 2005 at 21:53
    Reply | Quote | #1

    I want to use this formula to calculate the following:

    Age Groups:
    0-13
    14-17
    18-65+

    I tried =COUNTIF(A3:A30,”13″ for 14-17 years

    and you may be able to see I’ll run into some trouble trying to use this to calculate the total amount of 18-65+ clients.

    Any ideas?
    Kind regards,
    Topaz

  2. mark
    July 24th, 2005 at 05:55
    Reply | Quote | #2

    You are wanting to count by multiple criteria (greater than 13 and less than 18 is one age group), which countif isn’t good for.

    Jwalk has a nice list of how to count by multiple criteria.

    Here’s a sample formula that looks in A1:A10 and gives you the count of ages in the age group 14-17 (enter this with ctrl+shift+enter):

    =SUM((A1:A10>=13)*(A1:A10<=18))

  3. 1 trackbacks
    Show Hide
    1. 2008/10/13 - VBA: Highlight Duplicates in a Range