Want to learn how to generate a two-way summary count using the Excel **COUNTIFS **function? This post will give you an overview of how to do so using the **COUNTIFS **function in Excel.

**Formula**

The generic formula to perform the two-way summary count with COUNTIFS function is:

**=COUNTIFS(criteria_rng1,criteria1,criteria_rng2,criteria2)**

**Explanation**

Excel COUNTIFS function counts the cells based on more than two criteria. So using this function you can generate a summary of counts based on two dimensions.

To generate the two-way summary count using the COUNTIFS function, follow the steps below:

- Click on
**Formulas tab**on the menu bar in Excel. - Click on
**More Functions**and point the cursor on**Statistical** - Scroll down the list that displays and click on
**COUNTIFS.**

The dialogue box will display as shown below:

* Figure 1: How to access COUNTIFS formula in Excel*

**criteria_range1**is the name of the range where criteria 1 is tested**criteria_range2**is the name of the range where criteria 2 is tested.- Click
**OK.**

**Example**

In this example, the first table containing the raw data and a second table containing the column headings is used to count the number of employees and managers in each department.

Inside the** COUNTIFS **function, the first range is the range of cells you want evaluated for, which is **D6:D13** with the name** “office”** and the criteria1 is in G6, written as **“$G6”, **(relative reference) so that the formula changes when copied from one position to another. Criteria-range2 extends from **E6:E13,** with the name **“divis”** and criteria2, is in H5, written as **H$5 **(absolute reference) to keep it from changing when copied from one position to another.

**=COUNTIFS(office,$G6,divis,H$5)**

When this formula is replicated through the table, the references generate a new formula so the **COUNTIFS **gets a different result.

It will show you the result displayed on the screenshot below:

*Figure 2: Result displayed by COUNTIFS*

How to **NAME **a range in Excel

- First, select the row containing the data you want to name,
- Then, go to the
**home tab**and click on**formulas,** - Go to
**define name** - Delete the existing name and type in the desired name
- Click
**OK**

The dialogue box will display as shown in the screenshot below:

*Figure 3: How to name a range in Excel*

## Leave a Comment