📌 Stage 2 · Lead Intake · Sales Pipeline CRM · Lead Analytics · 30–40 min

COUNTIF turns the log into insight

Learning goals

  • Count rows with COUNTIF and non-blank entries with COUNTA
  • Compute a conversion rate and read it without fooling yourself
  • Compare channels by conversion, not by volume

Concepts

COUNTIF and COUNTA - the two workhorses

The intake log becomes insight the moment you count it. COUNTIF takes a range and a criterion and returns how many cells match; COUNTA counts how many cells are not blank, which is the honest denominator when rows accumulate over time. Together they turn twelve typed rows into a conversion rate.

=COUNTIF($F$5:$F$1004,"Converted")

Count every row of the Status column (F) that reads Converted. Result: 8. Because the range runs to row 1004, the same formula still works when the log holds three hundred leads.

=COUNTA($B$5:$B$16)

Count non-blank lead numbers in B5:B16 - the total number of logged leads. Result: 12. COUNTA ignores nothing that is typed; COUNT would ignore text, which is why COUNTA is the one used on ID columns.

💡 Tips:

  • Criteria are text and case-insensitive: "Converted" and "converted" match the same rows - but the dropdown makes the data uniform anyway.

8 of 12, and what the rate hides

Eight of twelve leads converted - 67%. Sound spectacular? Slice it by source before celebrating: Referrals went 3 for 4, Trade Show 2 for 2, Walk-In 1 for 1, Cold Call 1 for 1, LinkedIn 0 for 1 (still nurturing), and the Website went 1 for 3 - one conversion, one disqualified bookstore, one brand-new yoga studio still pending. The honest reading: the website generates volume but needs qualifying work, while referral and trade-show traffic converts nearly everything.

Two cautions from the small-sample trenches. First, count the rows before quoting a percentage - '100% of cold calls converted' is one deal, not a channel strategy. Second, two of the twelve leads are still alive (New and Working), so the 67% is a floor, not a final grade.

💡 Tips:

  • When someone quotes you a conversion rate, ask the denominator. 8/12 and 8/120 are different businesses.

Practice

Fixed case (Jan-Jun 2026, aging date Jun 30, 2026). Pale-green cells are formulas; white cells are typed entries. This lesson opens on Leads; C18 below the table is your scratch cell.

  1. Count the conversions — Click C18 (blank, just below the table) and type =COUNTIF($F$5:$F$1004,"Converted") then Enter. The cell returns 8.
  2. Vary the criterion — Edit the formula to count "Nurture" (1), then "Disqualified" (1), then "New" (1) and "Working" (1). Eight conversions plus four live or parked leads = twelve rows accounted for.
  3. Size the denominator — In the same cell type =COUNTA($B$5:$B$16) - it returns 12. The conversion rate is 8 divided by 12, about 67%.
  4. Slice one channel — Replace the formula with =COUNTIFS($E$5:$E$1004,"Website",$F$5:$F$1004,"Converted") - it returns 1: the website's single conversion against three inquiries. Referrals by contrast went 3 for 4.

Online exercise

The spreadsheet below is editable — follow the practice steps right on this page. Steps that need ribbon menus should be done in your own Excel with the downloadable practice file.

表格加载中…

Checklist

Work through each item; when every box passes, this lesson is done:

  • ☐ C18 returned 8 Converted leads out of 12 logged (COUNTA on column B)
  • ☐ Computed the website channel at 1 conversion of 3 inquiries with COUNTIFS
  • ☐ Can explain why the 67% headline rate hides the channel story until you slice it