Microsoft Excel: Build A Table That Will Count By Criteria

Problem: You need to build a summary table using COUNTIF functions. How can you enter one formula that can be copied?

See all Microsoft Excel tips

Strategy: It is possible to use a cell reference as the second argument in the COUNTIF function. Set up a table below your data. Place all of the possible values for a column, such as department, in column A, as shown in Fig. 565.

1) In column B of the first row, enter =COUNTIF($B$2=$B$56, A61), as shown in Fig. 566. Note that you should use the F4 key to make the B2:B56 range absolute. This will allow you to copy B61 to cells B62:B65.

2) Double-click the Fill handle to copy the formula down to B62:B65. II

Result: As shown in Fig. 567, the table shown below provides a summary of your dataset.

Summary: Using COUNTIF with a cell reference as the second argument allows you to set up various tables to summarize your data by department, gender, or any field.

Functions Discussed: =COUNTIF()