Special

Excel Functions

2003-2010

Special

Excel Functions

2007-2010

Lesson 14: COUNTIF  Function in Excel

The general format for the COUNTIF formula is as follow: =COUNTIF(range where criteria applies, criteria, count what).

In this first example the criteria applies to the range that is counted:
=COUNTIF(D2:D7,">5",D2:D7)
In plain English it says count the values of the cells D2 to D7 if they are greater than 5. The result should be 3.

The criteria can also be in another column. Applied to the table above, the result of the following formula should be 3:
=COUNTIF(C2:C7,"John" ,D2:D7)
or
=COUNTIF(C2:C7,C2 ,D2:D7)
In plain English it says: count the values of the cells D2 to D7 if the value in cells C2 to C7 is John or equivalent to the value in cell C2.

If you want to count the amounts based on month, State AND name, discover the very simple SUMPRODUCT  function with which you can use 2, 3 or more criteria.

Excel Functions and Formulas Sitemap

Go to the next lesson:
Lesson 15: SUMIF