Territory rollup: region and segment together (SUMIFS)
The problem
Problem statement
The forecast sheet needs one cell per territory, and a territory is a region plus a segment. The log is in A2:C7, the region is F1 and the segment is F2. Put the total in the Total cell F3.
Both conditions must come from cells — a hardcoded "East SMB" would not survive the next territory.
Problem data
Show the sheet as a table (selectable, copyable)
| A | B | C | E | F | |
|---|---|---|---|---|---|
| 1 | Region | Segment | Amount | Region | East |
| 2 | East | SMB | 300 | Segment | SMB |
| 3 | West | SMB | 250 | Total | |
| 4 | East | Enterprise | 900 | ||
| 5 | East | SMB | 250 | ||
| 6 | North | SMB | 400 | ||
| 7 | West | Enterprise | 700 |
Notes
Two criteria pairs in one SUMIFS is an AND: a row must match both to be added. The common mistake is summing the whole region and subtracting the other segment by hand — that breaks the moment a third segment appears.
How to approach
Your answer goes in F3.
- Target cell is F3
- SUMIFS takes the sum range first, then range/criteria pairs
- Pair 1: A2:A7 with F1; pair 2: B2:B7 with F2
- Shape: =SUMIFS(?, ?, ?, ?, ?)
Function guide
FAQ
How do I sum rows that match two conditions?
=SUMIFS(amounts, col1, crit1, col2, crit2). Every pair must match (AND), and all ranges must be the same size.
Why is my territory total too high?
Usually only one condition is applied — the segment pair is missing or points at the wrong column. Check that both ranges are the same height as the sum range.