Three Approaches for Counting Data Instances in Excelby
Most of your spreadsheets likely entail summing amounts for various reasons. However, sometimes we need to count the number of instances, rather than sums.
In this article, I’ll compare using COUNTIF, COUNTIFS, and a pivot table for counting the number of instances within a dataset. Figure 1 shows a data set that lists salaries by region, department, and whether they are contract employees or not.
Let’s say that we want to determine the headcount by department. In this case, we’ll first need to get a listing of one of each department name. To do so:
- Right-click column C.
- Choose Copy from the menu that appears.
- Right-click any other column, such as column H.
- Choose Paste from the menu that appears.
- Activate Excel’s Data menu.
- Choose the Remove Duplicates command.
- Click OK within the Remove Duplicates dialog box.
- Click OK on the prompt that signifies how many unique items remain.
Figure 1: The duplicates must be removed before determining the headcount by department.
You’re now ready to use the COUNTIF function to tally the headcount by department. Enter this formula in cell I2:
Notice in this case I omitted the row numbers from the reference to column C. Doing so future-proofs the formula, so that if I later add more records to the bottom the COUNTIF function will include these as well.
This also means that you don’t need to include dollar signs to indicate an absolute reference. The formula would look like this if we referenced the exact cells:
The dollar signs instruct Excel not to change the row numbers as we copy the formula down. As you may have surmised, the COUNTIF function has two arguments:
- range -- The range of cells to be searched
- criteria -- The item being sought within the range
Figure 2: The COUNTIF function tallies the headcount by department.
Now, you may wish to extend this headcount to tally the employees by both department and region. In this situation use the Remove Duplicates feature as shown above to create a unique list of region names.
Let’s say that the completed list appears in cells in M1 through M5. We can transpose these values to become column headings:
- Select cells M2:M5.
- Choose the Copy command (or press Ctrl-C).
- Right-click cell I1.
- Choose the Transpose command from the menu. In Excel 2007 you’ll need to choose Paste Special, click the Transpose checkbox, and then click OK.
You can now erase column M or the temporary range that you used.
Figure 3: Transpose the region names to prepare your data to use the COUNTIFS function.
At this point we’ll need to use the COUNTIFS function since we need to count based upon two criteria. Enter this formula in cell I2:
Notice that we need many more $ signs in this case because we’re copying the formula down and across. The COUNTIFS function allows you to specify up to 128 criteria pairs, which is a combination of the aforementioned range and criteria arguments I discussed for COUNTIF.
Figure 4: Use the COUNTIFS function to tally both the departments and regions.
A third approach involves using a pivot table to summarize this data. In this case, Excel will manage all of the heavy lifting:
- Click any cell within the list, such as cell A1.
- Activate Excel’s Insert menu.
- Choose the PivotTable command.
- Click OK when the Create Pivot Table command appears.
- Click Department within the PivotTable Fields list.
- Drag Region into the Columns section of the PivotTable Fields list.
- Drag Region into the Values section of the PivotTable Fields list.
- Excel instantly creates a summary that shows one of each department and region.
Figure 5: Use a pivot table to count the data.
Pivot tables provide several benefits over using worksheet functions:
- There’s no need to remove duplicates as Excel automatically displays one of each item.
- There are no formulas to write as Excel summarizes the data automatically.
- Excel adds grand total rows and columns as needed, which you can manage by way of the Grand Totals command that appears on the Design menu that is present when you select any cell within a pivot table.
David H. Ringstrom, CPA, is an author and nationally recognized instructor who teaches scores of webinars each year. His Excel courses are based on over 25 years of consulting and teaching experience. His mantra is “Either you work Excel, or it works you.” David offers spreadsheet and database consulting services nationwide.