Three Approaches for Counting Data Instances in Excel

Spreadsheets and graphs on a desk
xfgiro/istock

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:

  1. Right-click column C.
  2. Choose Copy from the menu that appears.
  3. Right-click any other column, such as column H.
  4. Choose Paste from the menu that appears.
  5. Activate Excel’s Data menu.
  6. Choose the Remove Duplicates command.
  7. Click OK within the Remove Duplicates dialog box.
  8. Click OK on the prompt that signifies how many unique items remain.

Please Login or Register to read the full article

To access all of the content on our site, register (it's free!) or login to your existing account.

Replies

Please login or register to join the discussion.

There are currently no replies, be the first to post a reply.