To count numbers or dates that meet a single condition (such as equal to, greater than, less than, greater than or equal to, or less than or equal to), use the COUNTIF function. To count numbers or dates that fall within a range (such as greater than 9000 and at the same time less than 22500), you can use the COUNTIFS function. Alternately, you can use SUMPRODUCT too.
Example
Note: You'll need to adjust these cell formula references outlined here based on where and how you copy these examples into the Excel sheet.
|
1 |
A |
B |
|---|---|---|
|
2 |
Salesperson |
Invoice |
|
3 |
Buchanan |
15,000 |
|
4 |
Buchanan |
9,000 |
|
5 |
Suyama |
8,000 |
|
6 |
Suyma |
20,000 |
|
7 |
Buchanan |
5,000 |
|
8 |
Dodsworth |
22,500 |
|
9 |
Formula |
Description (Result) |
|
10 |
=COUNTIF(B2:B7,">9000") |
The COUNTIF function counts the number of cells in the range B2:B7 that contain numbers greater than 9000 (4) |
|
11 |
=COUNTIF(B2:B7,"<=9000") |
The COUNTIF function counts the number of cells in the range B2:B7 that contain numbers less than 9000 (4) |
|
12 |
=COUNTIFS(B2:B7,">=9000",B2:B7,"<=22500") |
The COUNTIFS function counts the number of cells in the range B2:B7 greater than or equal to 9000 and are less than or equal to 22500 (4) |
|
13 |
=SUMPRODUCT((B2:B7>=9000)*(B2:B7<=22500)) |
The SUMPRODUCT function counts the number of cells in the range B2:B7 that contain numbers greater than or equal to 9000 and less than or equal to 22500 (4). |
|
14 |
Date |
|
|
15 |
3/11/2011 |
|
|
16 |
1/1/2010 |
|
|
17 |
12/31/2010 |
|
|
18 |
6/30/2010 |
|
|
19 |
Formula |
Description (Result) |
|
20 |
=COUNTIF(B14:B17,">3/1/2010") |
Counts the number of cells in the range B14:B17 with a data greater than 3/1/2010 (3) |
|
21 |
=COUNTIF(B14:B17,"12/31/2010") |
Counts the number of cells in the range B14:B17 equal to 12/31/2010 (1). The equal sign is not needed in the criteria, so it is not included here (the formula will work with an equal sign if you do include it ("=12/31/2010"). |
|
22 |
=COUNTIFS(B14:B17,">=1/1/2010",B14:B17,"<=12/31/2010") |
Counts the number of cells in the range B14:B17 that are between (inclusive) 1/1/2010 and 12/31/2010 (3). |
|
23 |
=SUMPRODUCT((B14:B17>=DATEVALUE("1/1/2010"))*(B14:B17<=DATEVALUE("12/31/2010"))) |
Counts the number of cells in the range B14:B17 that are between (inclusive) 1/1/2010 and 12/31/2010 (3). The DATEVALUE function converts the dates to a numeric value, which the SUMPRODUCT function can then work with. |
