< Go Back

Two-way summary count with COUNTIFS

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

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar