Home » Posts

Two-way summary with SUMIFS

To build a two-way summary table that sums numeric data with more than one condition, 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.

April 14, 2025 · 1 min · 66 words · Michael Roberts

Table of Contents

Explanation

TheSUMIFS functionis designed to sum numeric values using multiple criteria.

This allows the formula to change as needed when the formula is copied throughout the table.

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

Excel formula: SUMIFS with Excel Table

Without names ranges

This example uses named ranges for convenience only.

Note: apivot tablewould also be an excellent way to solve this problem.

Excel SUMIFS function

Explanation#

Without names ranges#

Explanation

Without names ranges