Formula Fundamentals: Beyond SUM and AVERAGE

The Formula That Saved 40 Hours

A finance team manually categorized 3,000 expense transactions every month. “Under $100? Petty cash. Over $100 and under $5,000? Manager approval. Over $5,000? VP approval.” Forty hours of work per month.

One nested IF formula categorized all 3,000 transactions in less than a second. =IF(A2<100, "Petty Cash", IF(A2<5000, "Manager", "VP")). The team reclaimed a full work week every month.

By the end of this lesson, you’ll write conditional formulas that automate decisions, count with criteria, and manipulate text—skills that transform spreadsheets from calculators into intelligent tools.

🔄 Quick Recall: In the previous lesson, we established that most users operate at Level 1-2 (SUM, AVERAGE, basic formatting). Today we jump to Level 2-3 with formulas that add logic and conditions to your spreadsheets.

The IF Function: Teaching Your Spreadsheet to Decide

The IF function has three parts: =IF(test, value_if_true, value_if_false)

Basic example: =IF(B2>90, "A", "Below A") — If the score in B2 is above 90, show “A”; otherwise show “Below A.”

Nested IF for multiple conditions: =IF(B2>90, "A", IF(B2>80, "B", IF(B2>70, "C", "F"))) — Grades A, B, C, or F.

Combining IF with AND/OR: =IF(AND(B2>80, C2="Completed"), "Pass", "Fail") — Pass only if score is above 80 AND status is “Completed.”

I need a formula that categorizes data based on
multiple conditions:
[describe your conditions]

Write the formula for both Excel and Google Sheets.
Explain each part so I understand how to modify it.

✅ Quick Check: How would you write an IF formula that labels sales as “High” if above $10,000, “Medium” if between $5,000 and $10,000, and “Low” if under $5,000?

The SUMIF Family: Conditional Math

SUMIF: Sum with One Condition

=SUMIF(range, criteria, sum_range)

Example: Sum all sales for the “North” region: =SUMIF(A2:A100, "North", C2:C100)

This sums values in column C where the corresponding cell in column A is “North.”

SUMIFS: Sum with Multiple Conditions

=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2)

Example: Sum sales for “North” region in “January”: =SUMIFS(C2:C100, A2:A100, "North", B2:B100, "January")

COUNTIF and COUNTIFS: Count with Conditions

Same logic, but counts instead of sums:

=COUNTIF(A2:A100, "North") — How many entries are in the North region? =COUNTIFS(A2:A100, "North", D2:D100, ">1000") — How many North region entries have values over 1000?

AVERAGEIF: Average with Conditions

=AVERAGEIF(A2:A100, "North", C2:C100) — Average sales for the North region only.

Function	What It Does	Example Use
SUMIF	Sum values meeting one condition	Total sales by region
SUMIFS	Sum values meeting multiple conditions	Total sales by region AND month
COUNTIF	Count cells meeting one condition	Number of orders per product
COUNTIFS	Count cells meeting multiple conditions	Orders per product per quarter
AVERAGEIF	Average values meeting one condition	Average price by category

✅ Quick Check: You have a list of employees with departments and salaries. How would you calculate the average salary for the Marketing department only?

Text Functions: Working with Words

Spreadsheets handle text as well as numbers. These functions are essential for cleaning and combining data.

CONCATENATE (or &)

Combine text from multiple cells: =A2 & " " & B2 — Combines first name and last name with a space.

In newer versions: =TEXTJOIN(" ", TRUE, A2, B2) — More flexible for multiple cells.

LEFT, RIGHT, MID

Extract parts of text:

=LEFT(A2, 3) — First 3 characters
=RIGHT(A2, 4) — Last 4 characters
=MID(A2, 5, 3) — 3 characters starting from position 5

TRIM and CLEAN

Remove unwanted spaces and characters:

=TRIM(A2) — Removes extra spaces
=CLEAN(A2) — Removes non-printable characters

These are essential when data comes from imports, web scrapes, or copy-paste operations.

UPPER, LOWER, PROPER

Standardize text case:

=UPPER(A2) — ALL CAPS
=LOWER(A2) — all lowercase
=PROPER(A2) — Title Case

Using AI to Write Formulas

The power of AI for spreadsheet work is translation: you describe what you want, AI writes the formula.

I have data in these columns:
- Column A: Employee names
- Column B: Department
- Column C: Salary
- Column D: Start date
- Column E: Performance rating (1-5)

Write formulas for:
1. Total salary for the Marketing department
2. Number of employees with rating 4 or higher
3. Average salary for employees who started
   after 2023
4. Full name combined from first name (Col F)
   and last name (Col G)
5. Salary categorized as "High" (>$100K),
   "Medium" ($60K-$100K), or "Entry" (<$60K)

Give me both Excel and Google Sheets versions
if they differ.

Common Formula Errors and Fixes

Error	Meaning	Common Fix
#REF!	Reference to a deleted cell	Check cell references
#VALUE!	Wrong data type in formula	Check for text in number fields
#N/A	Lookup value not found	Check for typos or extra spaces
#DIV/0!	Dividing by zero	Wrap with IFERROR or IF check
#NAME?	Function name misspelled	Check spelling

The IFERROR safety net: =IFERROR(your_formula, "Error") — Shows “Error” instead of ugly error codes.

Try It Yourself

Open a spreadsheet and create a small dataset: 10 rows with Name, Department, Sales Amount, and Month columns. Then write these formulas:

=SUMIF to total sales for one department
=COUNTIFS to count entries for a specific department in a specific month
A nested =IF to categorize sales as High/Medium/Low
=AVERAGEIF to find average sales per department

If you get stuck, paste your data description into AI and ask it to write the formula.

Key Takeaways

IF functions add decision-making logic to your spreadsheet, enabling automated categorization and conditional responses
SUMIF/COUNTIF/AVERAGEIF let you calculate based on criteria without manually filtering data
SUMIFS handles multiple criteria simultaneously, making it more powerful than SUMIF for complex analysis
Text functions (CONCATENATE, TRIM, LEFT/RIGHT) are essential for cleaning and preparing data
IFERROR wraps any formula with a safety net against ugly error displays
AI translates plain English descriptions into working formulas instantly

Up Next

In Lesson 3: Data Organization and Table Structure, we’ll learn how to structure your data properly—because even the best formulas fail on badly organized data.