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. 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: The first part of the formula sums opportunities by salesperson:
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:
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: 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.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
