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 (available in Excel 2007 and later) 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). You can use this function in Excel 2003 and earlier, where COUNTIFS is not available.

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). This example serves as a substitute for the COUNTIFS function that was introduced in Excel 2007. The DATEVALUE function converts the dates to a numeric value, which the SUMPRODUCT function can then work with.

Need more help?

Expand your skills
Explore Training
Get new features first
Join Microsoft Insiders

Was this information helpful?

What affected your experience?

Any additional feedback? (Optional)

Thank you for your feedback!

×