Three Approaches for Counting Data Instances in Excel

Spreadsheets and graphs on a desk

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.

BONUS: If you register now you can opt to receive a digital copy of "Transform!" , Richard Francis' new book for growing firms [US/Canada ONLY].

About David Ringstrom, CPA

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.


Please login or register to join the discussion.

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