MrExcel.com - Your One Stop for Excel Tips & Solutions

Build a Table That Will Count by Criteria

March 28, 2022 - by

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.

A different way to use COUNTIF is to enter departments in A1:A4, such as Accounting, Manufacturing, Marketing, Sales. The formula in B1 is =COUNTIF($E$7:$E$62,A1)
Figure 404. Count of records by department.

This article is an excerpt from Power Excel With MrExcel

Title photo by Anne Nygård on Unsplash