What you will build

Build a small CRM with fictional data: each lead has a stable Lead ID, company, standardized Stage, and Next action. Then use COUNTIF to quickly count leads currently at the Qualified stage.

Learning objective

Learn to organize a simple lead table, use a consistent Stage vocabulary, enter COUNTIF in the exact result cell, and verify how the formula reacts when a Stage changes.

Prepare the sample data

Open the exact source workbook below. It uses the Example tab and fictional data, so no real customer information appears. The source includes a demonstration result in H2; on your own copy, clear H2 before re-entering the formula later in the lesson.

HANDS-ON PRACTICE

Practice in your own copy

Make your own Google Sheets copy with the same data, formulas, formatting, filter and frozen header. The original remains unchanged.

Make a Google Sheets copy (access required) ↗View original

Sign in to Google. If the original is not publicly shared, download the full workbook and import it into Google Sheets, or paste the example data into your own sheet.

The templates-04 source workbook in Google Sheets, with the Example tab showing all three sample leads and the Formula result area.

Open the exact source workbook in Google Sheets ↗

Work through the example

Step 1: On your copy, keep Lead ID as stable codes L01, L02, and L03. Do not use row numbers as identifiers because inserting, deleting, or sorting rows can change row numbers.

Step 2: Keep Stage values in one consistent vocabulary: New, Contacted, and Qualified. Exact spelling lets COUNTIF count reliably.

Step 3: Clear H2 on your copy, select H2, and enter the formula below once. This formula returns one summary value, so there is no fill or copy-down step.

Formula to try

=COUNTIF(C2:C4,"Qualified")
The _result workbook with H2 selected, the formula bar showing the complete COUNTIF(C2:C4,"Qualified") formula, and the result equal to 1.

Checkpoint

Lead IDCompanyStageNext action
L01Acme DemoNewSchedule call
L02North DemoContactedSend summary
L03River DemoQualifiedPrepare quote

After restoring the sample data, H2 should equal 1 because only L03 is at the Qualified stage.

Common mistakes and fixes

If you use variants such as qualified, a Qualified value with extra spaces, or a different Stage name, exact criterion matching will not treat them as the same value.

Independent practice

Temporarily change C2 from New to Qualified and confirm H2 increases from 1 to 2; then restore C2 to New and confirm H2 returns to 1.

Key takeaways

  • ✓Use stable Lead IDs instead of row numbers to identify leads.
  • ✓Keep Stage values in a consistent vocabulary so counts remain reliable.
  • ✓COUNTIF(C2:C4,"Qualified") counts leads whose Stage is exactly Qualified.
  • ✓With the sample data, only L03 is Qualified, so H2 equals 1.
  • ✓This is one summary formula in H2; it does not need to be dragged or copied down.
QUICK CHECK

Check your understanding

Answer four questions about the practice sheet.

1. Why should the CRM use stable Lead IDs?
2. Which range does COUNTIF test?
3. What is the initial result in H2?
4. What does H2 become if C2 changes from New to Qualified?

Continue learning

Previous lesson: Inventory tracker. Next lesson: Invoice tracker.