Excel: Build a Table That Will Count by Criteria

This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel - 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.


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.

  1. Count of records by department.