Quick, clean, and to the point

Two-way summary count with COUNTIFS

Excel formula: Two-way summary count with COUNTIFS
Generic formula 

To build a two-way summary count (i.e. summarizing by rows and columns) you can use the COUNTIFS function.

In the example shown, the formula in G5 is:


How this formula works

The COUNTIFS function is designed to count things based on more than one criteria. In this case, the trick is to build a summary table first that contains one set of criteria in the left-most column, and a second set of criteria as column headings.

Then, inside COUNTIFS function, range1 is the named range "dept" (C5:C11) and the criteria comes from column F, input as the mixed reference $F5 (to lock the column). Range2 is the named range "class" (D5:D11) and the criteria comes from row 4, input as the mixed reference G$4 (to lock the row):


When this formula is copied through the table the mixed references change so that COUNTIFS will generate a count as the intersection of each "pair" of intersecting criteria.

Dave Bruns

Excel Formula Training

Formulas are the key to getting work done in Excel. In this step-by-step training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Start building valuable skills with Excel formulas today. Learn more.

Just a brief view of your website, and one specific idea - a "helper" value column - allowed me to do in less than two hours something I had been going down dead-ends with for two days. Thank you, thank you, thank you! -Roberta
Excel foundational video course
Excel Pivot Table video training course
Excel formulas and functions video training course
Excel Shortcuts Video Course
Excel Charts video training course