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.

About David Ringstrom

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.

Replies

Please login or register to join the discussion.

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