Lesson 14: COUNTIF Function in Excel
|
|
A
|
B
|
C
|
D
|
|
1
|
Month
|
State
|
Name
|
Amount
|
|
2
|
May
|
MI
|
John
|
2
|
|
3
|
Jun
|
NY
|
Peter
|
6
|
|
4
|
Mar
|
PA
|
John
|
8
|
|
5
|
Dec
|
NH
|
Peter
|
3
|
|
6
|
Nov
|
RI
|
John
|
4
|
|
7
|
Oct
|
FL
|
Peter
|
6
|
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.
COUNTIF Function in Excel
Links to the Top of the Page
Excel Functions and Formulas Sitemap
Downloadable Tutorials, Examples and Templates
|
