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.
The generic formula to perform the two-way summary count with COUNTIFS function is:
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.
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.
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