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.