- Excel Book Excerpt
Excel Build a Table That Will Count by Criteria
This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
Build a Table That Will Count by Criteria
Problem: I need to build a summary table using COUNTIF functions. How can I enter one formula that can be copied?
Strategy: Use a cell reference as the second argument in the COUNTIF function. Here’s how:
1. Set up a table below your data and place all the possible values for a column, such as department, in column A.
2. In column B of the first row, enter =COUNTIF($E$7:$E$62,A1). Note that you should press the F4 key after selecting E7:E62 to make the first range absolute. This will allow you to copy the formula to other rows.
3. Copy the formula down for the other departments.
Figure 411 Count of records by department.
For more resources for Microsoft Excel: