Exceljet

Quick, clean, and to the point

Two-way summary with SUMIFS

Excel formula: Two-way summary with SUMIFS
Explanation 

To build a two-way summary table that sums numeric data with more than one criteria, you can use the SUMIFs function. In the example shown, the formula in H5, copied across range H5:K7, is:

=SUMIFS(value,name,$G5,stage,H$4)

where value (C5:C15), name (B5:B15), and stage (D5:D15) are named ranges. The result is a table that shows summary totals for each name by stage.

How this formula works

The SUMIFS function is designed to sum numeric values using multiple criteria.

In the example shown, the data in the range B5:E15 shows a sales pipeline where each row is an opportunity owned by a salesperson, at a specific stage. The formula in H5 is:

=SUMIFS(value,name,$G5,stage,H$4)

The first part of the formula sums opportunities by salesperson:

=SUMIFS(value,name,$G5 // sum by name
  • Sum range is the named range values
  • Criteria range 1 is the named range name
  • Criteria 1 comes from cell G5

Notice $G5 is a mixed reference, with the column locked and the row relative. This allows the formula to change as needed when the formula is copied throughout the table.

The next range/criteria pair in SUMIFS, sums by stage:

stage,H$4 // sum by stage
  • Criteria range 2 is the named range stage
  • Criteria 2 is H$4

Again, H$4 is a mixed reference, with the column relative and the row locked. This allows the criteria to pick up the stage values in row 4 as the formula is copied across and down the table.

With both criteria together, the SUMIFS function correctly sums the opportunities by name and by stage.

Without names ranges

This example uses named ranges for convenience only. Without named ranges, the equivalent formula is:

=SUMIFS($C$5:$C$15,$B$5:$B$15,$G5,$D$5:$D$15,H$4)

Notice references for name, value, and stage are now absolute references to prevent changes as the formula is copied across and down the table.

Note: a pivot table would also be an excellent way to solve this problem.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated 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. Instant access. See details here.