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.
About David Ringstrom
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.