Two-way summary with SUMIFS

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.