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:

*=COUNTIF(C:C,H2)*

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:

*=COUNTIF(C$1:C$42 ,H2)*

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*:

*=COUNTIFS($A:$A,I$1,$C:$C,$H2)*

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.

### Related content

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 (0)

## Please login or register to join the discussion.

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