📌 Stage 4 · Follow-Up Discipline · Sales Pipeline CRM · Coverage Check · 30–40 min

COUNTIFS finds the silent deals

Learning goals

  • Count with two criteria at once using COUNTIFS
  • Define coverage: an open deal needs at least one not-done activity
  • Run the weekly coverage check and act on what it finds

Concepts

Two criteria, one count

Lesson 6 counted one column; coverage needs two questions answered on the same row: how many activities belong to THIS opportunity, and how many of those are still open (Done = N)? COUNTIFS takes criteria in pairs - range, criterion, range, criterion - and counts rows where every pair matches.

=COUNTIFS(Activities!$E$5:$E$1004,$B11,Activities!$I$5:$I$1004,"N")

Count rows on Activities where the Opp # (column E) equals the code in B11 (OPP-107) AND the Done flag (column I) is N. Result: 0 - every logged touch on OPP-107 is finished and nothing new is promised.

💡 Tips:

  • COUNTIFS pairs always travel as (range, criterion). Mixing the order is the most common syntax error - Excel will usually refuse the formula rather than miscount.

Coverage: the weekly hygiene check

An open deal with zero open activities is not being worked - it is being remembered. Run the check across the six open deals: OPP-109 has two open touches (the overdue demo and the revised quote), OPP-110 one (the overdue site visit), OPP-111 one (pilot presentation due Jul 10), OPP-112 one (intro email due Jul 3). But OPP-107 - the $18,500 hotel renewal, the single biggest deal in the forecast - has zero. So does OPP-108 ($9,600, quote sitting since Jun 12). Coverage: 4 of 6.

The result is a to-do list, not a statistic: book the hotel-minibar pricing follow-up today, and nudge Bluebird's facilities manager. Silence on the biggest line item is the classic way forecasts die, and COUNTIFS catches it in one formula a week.

💡 Tips:

  • Schedule it: Friday afternoon, one formula, one question - which open deals have no next step? Two minutes, every week.

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 Opportunities; E18 below the table is your scratch cell.

  1. Type the coverage count — Click E18 (blank) and type =COUNTIFS(Activities!$E$5:$E$1004,$B11,Activities!$I$5:$I$1004,"N") then Enter. It returns 0 - OPP-107 has no open follow-up.
  2. Point it at the second gap — Edit $B11 to $B12 (OPP-108) - still 0. Two uncovered deals now: the two biggest proposals in the pipeline, both silent.
  3. Point it at a covered deal — Edit to $B13 (OPP-109) - it returns 2: the overdue patio demo (ACT-509) and the revised quote follow-up (ACT-514).
  4. Complete the census — Check $B14 through $B16 (OPP-110: 1, OPP-111: 1, OPP-112: 1). Final coverage: 4 of 6 open deals have a next step booked; OPP-107 and OPP-108 need calls today.

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:

  • ☐ E18 returned 0 for OPP-107 - no open follow-up on the biggest deal
  • ☐ Verified coverage of 4 of 6 open deals, with OPP-107 and OPP-108 as the gaps
  • ☐ Can write a COUNTIFS with two criteria pairs and run the weekly check from memory