You probably already have the data you need to run your business better. It's sitting in a sales export, a customer list, or a spreadsheet you update every Friday. What's usually missing isn't information… it's a fast way to ask it questions. "How much did we sell in the north region last month?" "Which customers haven't ordered since spring?" With the right ten formulas, those questions take seconds instead of an afternoon.
Each formula below includes a plain example you can adapt. Assume a sales sheet with columns for Date (A), Customer (B), Region (C), Product (D), and Amount (E).
1. SUM: the starting point
=SUM(E2:E500)
Adds up a range. Simple, but worth mentioning because many people still add cells one by one with plus signs, which breaks when rows are inserted.
2. SUMIFS: totals with conditions
=SUMIFS(E:E, C:C, "North", D:D, "Gift Box")
Totals the Amount column only where Region is North and Product is Gift Box. You can stack as many conditions as you need. This one formula replaces a lot of filtering and copy-pasting.
Tip: Put your conditions in cells instead of typing them into the formula. =SUMIFS(E:E, C:C, H1) lets you change H1 and see a new answer instantly.
3. COUNTIFS: count with conditions
=COUNTIFS(C:C, "North", E:E, ">100")
Counts how many sales in the North region were over 100 dollars. Great for questions like "how many orders" rather than "how much money."
4. AVERAGEIFS: averages with conditions
=AVERAGEIFS(E:E, D:D, "Gift Box")
Finds the average sale amount for a product. Useful for spotting which items lead to bigger orders.
5. SUMIFS with dates
=SUMIFS(E:E, A:A, ">="&DATE(2026,5,1), A:A, "<"&DATE(2026,6,1))
Totals sales for May 2026. The trick is using "greater than or equal to" the first day and "less than" the first day of the next month. This avoids problems with the last day of the month.
6. XLOOKUP: find a matching value
=XLOOKUP(H2, tblProducts[Product], tblProducts[Price], "Not found")
Looks up a product name in your price list and returns its price. XLOOKUP is available in Microsoft 365, Excel 2021, and newer. If you're on an older version, use INDEX and MATCH.
7. INDEX and MATCH: the reliable lookup
=INDEX(Prices!B:B, MATCH(H2, Prices!A:A, 0))
Does the same job as XLOOKUP and works in every version of Excel. The 0 at the end means "exact match," which is what you want almost every time.
8. IF: label things automatically
=IF(E2>=500, "Large", "Standard")
Tags each order based on a rule. You can then count or total by label. For more than two outcomes, use IFS:
=IFS(E2>=500, "Large", E2>=100, "Medium", TRUE, "Small")
9. IFERROR: clean up ugly errors
=IFERROR(XLOOKUP(H2, A:A, B:B), "Check name")
Lookups return errors when something isn't found. IFERROR swaps the error for a friendly message. Be careful, though. Hiding errors can also hide real problems, so use a message that tells you what to check.
10. TRIM: fix messy text
=TRIM(B2)
Removes extra spaces from text. If your lookups mysteriously fail even though the names look identical, a trailing space is often the culprit. Clean the column with TRIM first.
Bonus: UNIQUE and FILTER
If you have Microsoft 365 or Excel 2021 or later:
=UNIQUE(B2:B500)gives you a list of every customer, with no duplicates.=FILTER(A2:E500, C2:C500="North")shows only North region rows, and updates automatically.
A real example: a monthly summary in five minutes
Say you run a small catering business. Your bookings sheet has date, client, event type, and invoice amount. You want a summary for your monthly review.
- In a new sheet, list your event types down column A: Wedding, Corporate, Private Party.
- In B1, put the first day of the month. In C1, put the first day of the next month.
- In B2, enter:
=SUMIFS(Bookings!D:D, Bookings!C:C, A2, Bookings!A:A, ">="&$B$1, Bookings!A:A, "<"&$C$1) - In C2, use COUNTIFS with the same conditions to count bookings.
- Copy both formulas down.
Change the dates in B1 and C1 next month, and the whole summary updates. That's the kind of small system that gives you back time every month.
Common mistakes to avoid
Hard-coding numbers inside formulas. If a tax rate or target lives inside a formula, you'll forget to update it. Put it in its own labeled cell.
Mixing text and numbers. Amounts imported as text won't add up. If SUM returns zero on numbers you can see, check whether they're stored as text.
Merged cells in data. They look tidy but break sorting, filtering, and formulas. Use them only on presentation sheets, if at all.
Not using Excel Tables. Press Ctrl+T on your data to make it a Table. Formulas then expand automatically as you add rows.
Forgetting absolute references. When you copy a formula and the reference shifts when it shouldn't, add dollar signs, like $B$1, to lock it.
Where to go from here
These ten formulas cover a surprising amount of everyday business analysis. Practice them on your own data this week. Start with SUMIFS on your sales, then add a lookup to your price list.
Want to build real analysis skills?
If you'd like to go beyond individual formulas and learn how to clean data, build PivotTables, create clear charts, and set up reusable workbooks, our Advanced Excel for Business Analysis course walks through it step by step, using realistic small-business examples.