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).
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
Create a blank workbook or worksheet.
Select the example in the Help topic.
Important Do not select the row or column headers.
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.
The COUNTIF function counts the number of cells within a range that meet a single criterion (condition) that you specify.
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.