Count numbers or dates based on a test

Important notice for users of Office 2003    To continue receiving security updates for Office, make sure you're running Office 2003 Service Pack 3 (SP3). The support for Office 2003 ends April 8, 2014. If you’re running Office 2003 after support ends, to receive all important security updates for Office, you need to upgrade to a later version such as Office 365 or Office 2013. For more information, see Support is ending for Office 2003.

To count numbers or dates that meet a single test (such as equal to, greater than, less than, greater than or equal to, or less than or equal to), use the COUNTIF function. In Excel 2007 and later, 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. If you are using Excel 2003 or earlier, you can use the SUMPRODUCT function to count the numbers that fall within a range (COUNTIFS was introduced in Excel 2007).

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.

2. Select the example in the Help topic.
Important    Do not select the row or column headers.

3. Press CTRL+C.

4. In the worksheet, select cell A1, and press CTRL+V

To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

 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.

Function details

The COUNTIF function counts the number of cells within a range that meet a single criterion (condition) that you specify.

=COUNTIF(range, criteria)

The COUNTIFS function applies multiple criteria to cells across multiple ranges and then counts the number of times all criteria are met. You can use the COUNTIFS function to find multiple criteria on the same range, the same criteria on multiple ranges, or multiple criteria on multiple ranges.

=COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2,…)

In the COUNTIFS example, criteria_range1 is B2:B7, criteria1 is ">=9000", criteria_range2 is again B2:B7, and criteria2 is "<=22500". The same range of numbers is tested twice, but you could also test a number of different ranges with different criteria for each.

The SUMPRODUCT function, as shown in the example, can be used in Excel 2003 and earlier, where COUNTIFS is not available. In the SUMPRODUCT example, B2:B7>=9000 acts as the criteria_range1 and criteria1 pair, and B2:B7<=22500 acts as the criteria_range2 and criteria2 pair that are used in the COUNTIFS example.