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.