COUNTIF Function In Excel 2010

by on
14

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.

Advertisement

  • 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.

    • bilal wazir

      sir, if there is gape b/w range, eg in attendance sheet there is sunday then how we will range the attendance of this sheet?

  • 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.

    • Kunal

      HI,

      you can try with IFwith AND and count the respective data by using COUNTIF.
      =IF(AND(C2=96,B2=”A”),1,2) and apply COUNTIF for calculating 1.

  • Tim

    Can I apply to countif to a filtered column and have it count just the cells in view ( not the ones hidden by the filter)- at the moment even if a column of 100 cells is reduced to 40 (say) the countif still counts all 100

  • Allen

    Well since nobody on the Internet has found a way to do this COUNTIF by providing results into a single formula with output to a single cell I wanted to share.
    NOTE:
    1) To make this work using the &TEXT option you must ensure cell A1 is blank of all data.
    2) Since TEXT was not original designed in quite this way the text in “use special characters” from Adobe Latin Character Set from Windows Character Map. If you replace them with standard characters your formula will display as #VALUE indicating an error so paste from the text below to ensure best results–make sure your cell is wide enough for display.
    3) Since I only wanted to total letter Grades of A, B, C & D and University Transfer Credits not part of the GPA calculation (Example Prior Learning Recognition) I included a Latin TC. If you do not require this you may change the last COUNTIF ,”TC” to “F*” using regular characters then replace the final ʨ special character with a Latin F.
    =COUNTIF(L7:L49,”A*”)&TEXT(A1,”Ꭺ▬# “)&COUNTIF(L7:L49,”B*”)&TEXT(A1,”Ᏼ▬# “)&COUNTIF(L7:L49,”C*”)&TEXT(A1,”Ꮯ▬# “)&COUNTIF(L7:L49,”D*”)&TEXT(A1,”Ꭰ▬# “)&COUNTIF(L7:L49,”TC”)&TEXT(A1,”ʨ# “)

  • Chad

    I tried the same step but the result I got were all “0″, any idea why?

    • Allen

      The only thing you have to do is update the range of L7:L49 depending on which row and columns your letter grades are stored in.
      =COUNTIF(L7:L49,”A*”)&TEXT(A1,”Ꭺ▬# “)&COUNTIF(L7:L49,”B*”)&TEXT(A1,”Ᏼ▬# “)&COUNTIF(L7:L49,”C*”)&TEXT(A1,”Ꮯ▬# “)&COUNTIF(L7:L49,”D*”)&TEXT(A1,”Ꭰ▬# “)&COUNTIF(L7:L49,”TC”)&TEXT(A1,”ʨ# “)