Count numbers or dates based on a condition

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

 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

A

B

Salesperson

Invoice

Buchanan

15,000

Buchanan

9,000

Suyama

8,000

Suyama

20,000

Buchanan

5,000

Dodsworth

22,500

Formula

Description (Result)

=COUNTIF(B2:B7,">9000")

The COUNTIF function counts the number of cells in the range B2:B7 that contain numbers greater than 9000 (4)

=COUNTIF(B2:B7,"<=9000")

The COUNTIF function counts the number of cells in the range B2:B7 that contain numbers less than 9000 (4)

=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)

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

Date

3/11/2011

1/1/2010

12/31/2010

6/30/2010

Formula

Description (result)

=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)

=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").

=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).

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

Expand your Office skills
Explore training Get instant Excel help
Connect to an expert now
Subject to Got It terms and conditions