Spreadsheets remain the everyday tool for sales reporting. These ten formulas cover most of what analysts do with sales, targets and distributor data.
1. SUMIFS
=SUMIFS(D:D,A:A,"North",B:B,"Q1") adds sales for a region and a quarter. It is the backbone of most summary tables.
2. COUNTIFS
=COUNTIFS(A:A,"North",C:C,">1000") counts, for example, outlets above a sales threshold.
3. XLOOKUP
=XLOOKUP(A2,F:F,H:H,"Not found") finds a value in one column and returns the matching item from another. It replaces most VLOOKUP uses in newer Excel versions.
4. INDEX + MATCH
=INDEX(H:H,MATCH(A2,F:F,0)) works in every version and handles lookups to the left.
5. IFERROR
=IFERROR(C2/B2,0) avoids #DIV/0! in achievement and growth columns.
6. SUMPRODUCT
=SUMPRODUCT(B2:B6,C2:C6)/SUM(C2:C6) gives a weighted average, useful for scorecards.
7. Percentage change
=(C2-B2)/B2 formatted as a percentage shows growth month on month or year on year.
8. RANK
=RANK(B2,$B$2:$B$50,0) ranks territories or salespeople by performance.
9. Nested IF or IFS
=IF(B2>=100,"Exceeds",IF(B2>=90,"Meets","Below")) turns a score into a rating band.
10. DATEDIF
=DATEDIF(A2,B2,"M") returns complete months between two dates, handy for tenure or ageing reports.
Good habits
- Keep raw data in one sheet and reports in another.
- Use Excel Tables so ranges grow automatically.
- Lock ranges with $ when copying formulas.
- Check totals against a source system before sharing.
Putting them together: a simple monthly report
Imagine a data sheet with Region in column A, Month in column B, Product in column C and Sales in column D, and a report sheet that lists regions in column A with the target in column B. A basic performance table needs only three formulas.
| Report column | Formula in row 2 |
|---|---|
| C: Actual | =SUMIFS(Data!D:D,Data!A:A,A2,Data!B:B,$H$1) |
| D: Achievement | =IFERROR(C2/B2,0) |
| E: Rank | =RANK(D2,$D$2:$D$10,0) |
Cell H1 holds the month. Change it, and the entire table recalculates. This one design choice, keeping the reporting period in a single input cell, avoids editing formulas every month and removes a common source of error.
Pitfalls that cause wrong numbers
- Numbers stored as text. Values exported from other systems often look like numbers but are ignored by SUM and SUMIFS. Check by comparing COUNT with COUNTA on the column, then convert with VALUE or Text to Columns.
- Extra spaces. "North " and "North" do not match. Use
=TRIM(A2)to clean text before using it as a lookup key. - Approximate matches. VLOOKUP defaults to an approximate match when the last argument is omitted, which can return a wrong value without any warning. Always use FALSE or 0 for exact matches, as in
=VLOOKUP(A2,F:H,3,FALSE). - Different argument order. SUMIF puts the sum range last, while SUMIFS puts it first. This catches many people when they switch between the two.
- Averaging percentages. The average of regional achievement percentages is not the overall achievement. Use total actual ÷ total target, or a weighted average.
Use IFERROR with care
IFERROR is convenient for division by zero, but it hides every kind of error, including broken references and failed lookups. If a missing product code silently returns 0, your total will be understated with no sign of a problem. For lookups, prefer a visible message such as "Not found" so that gaps can be spotted and fixed.
Version and compatibility notes
- XLOOKUP needs Excel 2021 or Microsoft 365. If colleagues use older versions, use INDEX and MATCH.
- IFS is available from Excel 2019 onwards. Nested IF works everywhere.
- RANK still works, and newer versions also offer RANK.EQ and RANK.AVG, which differ in how they treat ties.
- DATEDIF does not appear in Excel's function suggestions, and Microsoft warns that its "MD" unit can give inaccurate results, so stick to "Y" and "M" for reports.
When to move beyond formulas
Formulas are ideal for fixed report layouts. For exploring data, a PivotTable is often faster: you can summarise thousands of rows by region and product in moments, then refine the view by dragging fields. When you repeatedly clean the same export every month, Power Query can record those steps and repeat them with a single refresh. Both are built into modern versions of Excel for Windows and worth learning after these ten formulas.
Not sure how to write one? The Excel Formula Generator builds these formulas from your cell ranges.
This article is general information, not professional advice. See the disclaimer.