## Explanation

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.*