Excel Formulas Every Analyst Should Know (SUM, AVERAGE, COUNT, IF)

What You Will Learn

• The 8 essential Excel formulas every analyst uses
• How to write each one with real examples
• The conditional variants: SUMIF, COUNTIF, AVERAGEIF
• How to combine formulas using IF and nested IFs

Why This Topic Matters

You can do 80% of real spreadsheet analysis with just eight formulas. Most beginners learn dozens of formulas and forget them. This lesson teaches the small set that actually shows up in every working analyst's daily life. Master these eight and the rest are variations on the same ideas.

The Sample Dataset

We reuse the orders table from lesson 12:

Assume the data lives in cells A1:E6 (with row 1 being headers).

1. SUM — Total of a Numeric Column

=SUM(D2:D6)

Result: 500 + 1200 + 750 + 300 + 900 = 3650

Use: Total revenue, total quantity, total anything numeric.

2. AVERAGE — Mean of a Numeric Column

=AVERAGE(D2:D6)

Result: 3650 / 5 = 730

Use: Average order value, average delivery time. Remember from lesson 29 that the average can be misleading if the data has outliers — also compute the median.

3. COUNT vs COUNTA vs COUNTIF

=COUNT(D2:D6)        → 5  (counts numeric cells)
=COUNTA(B2:B6)       → 5  (counts non-empty cells, including text)
=COUNTBLANK(D2:D6)   → 0  (counts empty cells)

Use COUNT for numbers, COUNTA for any non-empty cell, COUNTBLANK to find missing values.

4. COUNTIF — Count with a Condition

=COUNTIF(E2:E6, "paid")      → 3  (orders paid)
=COUNTIF(E2:E6, "pending")   → 1
=COUNTIF(C2:C6, "Mumbai")    → 2  (orders from Mumbai)

Use: How many orders are paid? How many from Mumbai? How many above ₹500?

=COUNTIF(D2:D6, ">500")      → 3  (orders with amount > 500)

For numeric conditions, put the operator in quotes: ">500", ">=750", "<>300" (not equal to 300).

5. SUMIF — Sum with a Condition

=SUMIF(C2:C6, "Mumbai", D2:D6)   → 800  (500 + 300)
=SUMIF(E2:E6, "paid", D2:D6)     → 2150 (500 + 750 + 900)

Syntax: SUMIF(range_to_check, condition, range_to_sum). The first range is where the condition is tested; the second is what gets summed when the condition is true.

6. AVERAGEIF — Average with a Condition

=AVERAGEIF(C2:C6, "Mumbai", D2:D6)  → 400  (800 / 2)
=AVERAGEIF(E2:E6, "paid", D2:D6)    → 716.67 (2150 / 3)

Use: Average order value for paid orders, average delivery time for one city.

7. IF — The Decision Formula

=IF(D2 > 500, "large", "small")

If the amount in D2 is greater than 500, return "large". Otherwise, return "small". For our data: D2=500 → "small". D3=1200 → "large".

Use: Tag orders as large/small, customers as new/returning, deliveries as on-time/late.

Nested IF — Multiple Branches

=IF(D2 >= 1000, "large",
    IF(D2 >= 500, "medium", "small"))

For D2=500 → "medium". For D2=1200 → "large". For D2=300 → "small".

8. IFERROR — Handle Errors Gracefully

=IFERROR(AVERAGEIF(C2:C6, "Pune", D2:D6), 0)

If there are no Pune orders, AVERAGEIF returns #DIV/0! because you cannot divide by zero. IFERROR catches that and returns 0 instead. Always wrap division-based formulas in IFERROR when the denominator might be zero.

Combining Formulas — Real Analysis

Real analyses combine these formulas. Example: "What share of total revenue came from paid orders?"

=SUMIF(E2:E6, "paid", D2:D6) / SUM(D2:D6)
→ 2150 / 3650
→ 0.589  (format as 58.9%)

Two formulas, one result: 58.9% of revenue came from paid orders. That is the kind of insight these simple formulas produce.

Line-by-Line Walkthrough: Paid-Order Share

1. SUMIF(E2:E6, "paid", D2:D6) → walks down E2:E6, finds rows where status = "paid" (rows 2, 4, 6), sums D2, D4, D6 → 500 + 750 + 900 = 2150.
2. SUM(D2:D6) → sums all amounts → 3650.
3. 2150 / 3650 → 0.589.
4. Format the cell as a percentage → 58.9%.

Each piece is simple. The power comes from combining them.

Common Mistakes

1. Hard-coding cell references. Writing =SUM(D2+D3+D4+D5+D6) works but does not update if you add a row. Always use ranges =SUM(D2:D6).
2. Forgetting quotes around text conditions. COUNTIF(E2:E6, paid) treats paid as a cell name. Write "paid".
3. Mixing up the order of SUMIF arguments. The condition range comes first, then the condition, then the sum range.
4. Not wrapping division in IFERROR. Division by zero produces ugly #DIV/0! errors that confuse readers.
5. Using IF for what should be a lookup. If you have 5+ branches, use VLOOKUP/XLOOKUP, not nested IFs.

Practical Exercise (10 minutes)

Using the sample data above, compute:

1. Total revenue from Chennai orders. (Expected: 2100)
2. Average order value for refunded orders. (Expected: 300)
3. Number of orders with amount ≥ 750. (Expected: 3)
4. Share of revenue from Mumbai. Express as a percentage. (Expected: ~21.9%)
5. Tag each order as "small" (<500), "medium" (500–999), or "large" (≥1000) using a nested IF.

Mini Challenge

Build a one-cell "order summary" that returns text like: "Total: ₹3,650 across 5 orders, avg ₹730, 60% paid." Use CONCATENATE or the & operator to join text and formula results. (Hint: ="Total: ₹" & SUM(D2:D6) & " across " & COUNT(D2:D6) & " orders...")

Key Takeaways