KPI Performance Calculator
Score performance with configurable KPI weights, targets, caps and higher/lower-is-better logic.
Excel Formula Generator
Pick what you want to do, enter your cell references and copy a working Excel formula with a plain-English explanation.
Use the Excel Formula Generator
Loading tool…
How to use the Excel Formula Generator
- Choose the type of formula from the list.
- Replace the example ranges and criteria with your own.
- Copy the formula and paste it into a cell in Excel or Google Sheets.
- Read the explanation to check the logic matches what you intended.
About this tool
The generator covers 18 everyday patterns: conditional sums and counts, lookups, rank, running totals, weighted averages, rating bands, percentage change and date differences. Formulas use standard Excel syntax and most also work in Google Sheets. XLOOKUP, UNIQUE and TEXTJOIN need a recent version of Excel.
Free to use. Your inputs are processed in your browser and are not sent to our servers. Results are estimates for general information; see the disclaimer.
Need a full report or dashboard built? I build Excel dashboards and automated reports for your data. See services and request a quote.
Formulas you can generate
| Task | Formula type |
|---|---|
| Add up values that match one or two conditions | SUMIF, SUMIFS |
| Count or average matching rows | COUNTIF, COUNTIFS, AVERAGEIF |
| Look up a value in a table | VLOOKUP, XLOOKUP, INDEX + MATCH |
| Return different results, or rating bands | IF, nested IF |
| Percentage change and target achievement | Simple percentage formulas |
| Weighted average | SUMPRODUCT |
| Rank, running totals, unique lists | RANK, cumulative SUM, UNIQUE |
| Dates and text | DATEDIF, TEXTJOIN |
VLOOKUP, XLOOKUP or INDEX + MATCH?
- XLOOKUP is the simplest and most flexible option, but it needs Excel 365 or Excel 2021 or later.
- INDEX + MATCH works in all versions and can look to the left, which VLOOKUP can't.
- VLOOKUP is widely known. Use exact match, or it can return a wrong answer when the list isn't sorted.
If your file will be shared with people on older versions, choose INDEX + MATCH.
How to use a generated formula
- Choose the task and enter your cell ranges exactly as they appear in your sheet, such as
A2:A500. - Copy the formula and paste it into the cell where you want the result.
- Before copying the formula down, press F4 to lock ranges that shouldn't move, turning
A2:A500into$A$2:$A$500. - Check one result by hand to confirm it's right.
Fixing common Excel errors
| Error | Usual cause | Fix |
|---|---|---|
| #N/A | The lookup value wasn't found | Check spelling and extra spaces, or wrap the formula in IFERROR |
| #VALUE! | Text where a number is expected | Convert text numbers to real numbers |
| #REF! | A referenced cell or column was deleted | Update the ranges in the formula |
| #DIV/0! | Division by zero or an empty cell | Check the inputs, or use IFERROR |
| #NAME? | A function your Excel version doesn't have, or a typo | Use an older alternative, such as INDEX + MATCH instead of XLOOKUP |
A tip on lookups: numbers stored as text and invisible extra spaces are the most common reasons lookups fail. TRIM removes extra spaces.
Excel for sales and operations reporting
SUMIFS, COUNTIFS and lookups cover most monthly reporting, from sales by region and month to achievement against target. For worked examples, read ten Excel formulas every sales operations analyst should know. To score several targets together, use the KPI Performance Calculator.
Frequently asked questions
Does it work in Google Sheets?
Most formulas do. XLOOKUP, UNIQUE and TEXTJOIN are supported in Google Sheets, while DATEDIF behaves slightly differently in some locales.
Why do text criteria need quotes?
Excel treats unquoted words as names or cell references. Put text in straight double quotes, such as "North".
Should I use VLOOKUP or XLOOKUP?
XLOOKUP is more flexible and safer when columns change, but needs Excel 2021 or Microsoft 365. VLOOKUP and INDEX-MATCH work in every version.
Does this work in Google Sheets?
Most formulas, such as SUMIFS, COUNTIFS, VLOOKUP, INDEX and MATCH, work the same way in Google Sheets. Newer functions can differ by version, so test them once.
Which lookup formula should I use?
Use XLOOKUP if everyone has Excel 365 or 2021 or later. Otherwise, INDEX plus MATCH works in all versions and is more flexible than VLOOKUP.
Why does my VLOOKUP return #N/A?
Usually the lookup value isn't an exact match. Check for extra spaces, numbers stored as text, or the wrong lookup column.
Related tools
Percentage Calculator
Percentage of a number, percentage change, percentage difference and add/subtract a percentage.
Business Report Generator
Turn highlights, metrics and actions into a structured business report you can copy or download as PDF.
Related articles
Ten Excel Formulas Every Sales Operations Analyst Should Know
Ten essential Excel formulas for sales and operations reporting: SUMIFS, COUNTIFS, XLOOKUP, INDEX-MATCH, IFERROR, SUMPRODUCT and more, with examples.
3 min read
Primary vs Secondary Sales in FMCG: Why the Difference Matters
Understand primary, secondary and tertiary sales in FMCG distribution, why secondary sales data matters, and how to keep it accurate.
4 min read