COUNTIF Function In Excel 2010

By Usman on Mar 04 2010 8 Comments

In Excel 2010 by using COUNTIF conditional logic, you will be able to count occurrence of data and show the result if the condition is met. It enables user to use a condition that contain two arguments (range,criteria), that would be applied on data which yields counting results, only if specified criteria is TRUE. Thus facilitating user to create a group for certain type of data that falls into specific category. This post explains simple usage of COUNTIF logic.

Launch Excel 2010, and open a datasheet on which you need to apply formula with COUNTIF function.

For Instance: We will use student grading datasheet, containing student records.

marks sheet

Now for checking how many students have secured A, B+, B, C+, C grade and how many failed in exam.

Here our primary concern is with Grade field, we will write a formula that will check the Grade column and count, how many students secured same grades.

Syntax:

=COUNTIF(range, criteria)

Now we apply formula complying with the syntax.

=COUNTIF(E2:E26,"A")

E2:E26 in the formula will select column E starting with row 2, to the end of the column E26. on selected column, a condition is applied that counts how many students secured A grade. This formula shows that 9 students secured A grade.

table 2[2]

Now we will create all other categories by changing the conditions that match with corresponding grades.

B+ Grade Students

=COUNTIF(E2:E26,"B+")

B Grade Students

=COUNTIF(E2:E26,"B")

C+ Grade Students

=COUNTIF(E2:E26,"C+")

C Grade Students

=COUNTIF(E2:E26,"C")

Fail Students (D grade)

=COUNTIF(E2:E26,"D")

The final Grading datasheet is shown in the screenshot below.

table 3[3]

You can also checkout previously reviewed SUMIF Function in Excel 2010.

Related Items

Excel 2010

  • Pingback: Guide On Excel 2010 Data Bars

  • Pingback: Excel 2010: Radians and Degrees Function

  • Pingback: Excel 2010: DCOUNT Function

  • Pingback: Excel 2010 Basic Arithmetic Operation On DATE Values

  • Pingback: FLOOR.PRECISE Function In Excel 2010

  • Devon

    This shouldn’t work. As an “A” grade is over 80, when the countif function goes to count “B” grades it will count all grades in the range over 75. An “A” grade is over 75 and will get counted with the “B” grades.

    • Usman

      Thanks for pointing it out. We have updated the guide.

  • Adam

    Would you be able to help me out with a query on how to create a condition that will identify in addition to identifing how many grade As bs etc, but also identifying the Lowest grade A mark, grade B mark and grade C mark?

    ive tried combining the COUNTIF statment with a MIN statement but so far unsuccesful.

    Any help would be greatly appreciated.

    Adam

  • mdafsra

    Please explain how to use vlookup and count if please briefly explain

    Md Afsar

  • Neer

    I am facing a shocking result of the countif function.
    =COUNTIF(A2:A300,”Amazon”)
    gives a wrong result of counting.
    like if actual is 28, its shows 29
    always 1 extra to the actual.

    Anyone know whats going on???

    Regards,

  • Pingback: Attendance Regularity: The important data hidden in your Children’s Ministry « RyanStigile.com

  • Maria

    How would you count a grade that is an A*. I have tried “A*” but that counts all the A and A*. I need just the A*.

    Thanks

    • JDH

      Use a ~ ahead of the * character in the formula … =countif(A1:A12, “A~*”)

  • Mike

    Is there a way to combine 2 CountIf functions, for example, count all that equal 96 and A? That would not be useful in this example of course, because all 96′s would by default be A’s. But for what I am attempting, it would be useful.