Sales by region roll-up template
By the Analistable team · Updated · 2 min read
Paste every region's sales lines into one Sales sheet, enter targets on the Targets sheet, and the Summary totals each region with SUMIFS, pulls its target with INDEX/MATCH, and shows variance, percent of target and share of total. It works in Excel and Google Sheets.
Part of our guide: How to consolidate data in Excel
What the template does
Regional managers tend to send their own sales sheet each month. The roll-up gives head office one table with every line, a region-level summary against target, and a check that nothing was lost in the copy-paste. It follows the stack-then-summarise method from the consolidation guide and pairs with combining monthly sales reports.
Sheet-by-sheet guide
| Sheet | Columns | You fill in? |
|---|---|---|
| Instructions | Step, What to do | No |
| Sales | Region, Rep, Month, Amount | Yes (paste each region's lines) |
| Targets | Region, Target | Yes |
| Summary | Region, Sales, Target, Variance, % of target, Share of total | No (formulas) |
How to fill it in
- Paste each region's export under the previous one on Sales. If a regional file has no Region column, fill it in for its block before pasting.
- Type the period's target for each region on Targets, spelled exactly as in Sales.
- Add a Summary row for any new region and copy the formulas from the row above.
- Check that the Check cell on the Total row is 0 before you send the report on.
How the formulas work
Each region's sales and target:
=SUMIFS(Sales!$D$2:$D$500,Sales!$A$2:$A$500,A2)
=IFERROR(INDEX(Targets!$B$2:$B$50,MATCH(A2,Targets!$A$2:$A$50,0)),0)Percent of target and share of total, rounded to one decimal:
=IFERROR(ROUND(B2/C2*100,1),0)
=IFERROR(ROUND(B2/$B$6*100,1),0)The Total row adds a check cell comparing the Summary total with the raw Sales total. If it is not 0, a Sales row has a region name the Summary does not list.
Worked example
| Region | Sales | Target | Variance | % of target | Share of total |
|---|---|---|---|---|---|
| North | 33700 | 32000 | 1700 | 105.3 | 29.1 |
| South | 26600 | 30000 | -3400 | 88.7 | 23 |
| East | 29200 | 28000 | 1200 | 104.3 | 25.2 |
| West | 26300 | 26000 | 300 | 101.2 | 22.7 |
| Total | 115800 | 116000 | -200 | 99.8 | 100 |
South is the only region behind target, and its gap of 3,400 is larger than the combined surplus of the other three regions, which is why the total lands 200 short. Share of total shows East carrying a quarter of sales from only two lines, so a single missed deal there moves the whole picture.
Limits and when to move on
- Region names must match exactly; "North " with a trailing space is a different region. See trailing spaces break lookups.
- Copy-pasting a dozen regional files each month gets slow. The Power Query folder method or the free consolidation tool stacks them for you.
- For questions like ranking reps across regional files, Analistable can load the original files and answer directly, without building more formulas.
Frequently asked questions
- Can I split the summary by month as well?
- Yes. Add a month condition to the SUMIFS, for example SUMIFS(Sales!D:D, Sales!A:A, A2, Sales!C:C, "Jul"), and put each month in its own column.
- What if a region has no target?
- The INDEX/MATCH returns 0 through IFERROR and % of target shows 0, so the gap is visible rather than an error.
- Does it work in Google Sheets?
- Yes. All formulas are standard SUMIFS, INDEX/MATCH, ROUND and IFERROR, which Google Sheets supports.