Excel templates / Sales summary
Sales summary template
An order list summed up by region and by product, with shares of the total and the biggest order, all with formulas.
Download the sales summary template (.xlsx). Free, no sign-up. Works in Excel 2010 and later, Excel for Mac, Excel on the web, Google Sheets and LibreOffice.
How to use it
- Replace the orders in columns A to F with your own (keep the headings). The summaries cover rows 2 to 21: to add more, insert rows above row 21 so the ranges grow with them.
- Rename the regions in
H3toH6and the products inH11toH14to match your data.
How it works
I3:I6=SUMIFS($F$2:$F$21,$B$2:$B$21,H3): Total sales for the region in column H: SUMIFS adds the Sales where the Region matches.J3:J6=COUNTIFS($B$2:$B$21,H3): How many orders came from that region.L3:L6=I3/$I$7: Each region’s share of all sales. $I$7 stays on the total as the formula is copied down.I11:L14=SUMIFS($F$2:$F$21,$C$2:$C$21,$H11,$B$2:$B$21,I$10): One formula fills the whole table: SUMIFS with two conditions, product and region. $H11 always looks at the product in column H, and I$10 at the region in row 10, so it can be copied across and down.I17=INDEX(D2:D21,MATCH(I16,F2:F21,0)): Who made the biggest order: MATCH finds the row with the biggest sale, and INDEX returns the rep on that row.
Learn the skills behind it
- SUMIF and AVERAGEIF
- COUNTIFS, SUMIFS and MAXIFS
- Mixed references
- INDEX and MATCH
- Absolute references with $
Functions used: SUMIFS, COUNTIFS, SUM, IF, MAX, INDEX, MATCH.