A step-by-step guide to computing sales account coverage in Excel from a CRM activity export and an account list: filtering activities to real touches, last touch per account with MAXIFS, days since last touch, the cadence owed by tier with a lookup, covered at cadence, coverage by count and by revenue per rep, the uncovered list ranked by value, and the check that every assigned account is in exactly one state. Includes the exact formulas and the point at which the spreadsheet stops being enough.
Account coverage is the share of a rep's accounts touched within the time each is owed. In Excel it is a lookup, a MAXIFS and two ratios. This guide gives the formulas and the check.
Sheet Activities, one row per logged activity:
| Column | Content |
|---|---|
| A | Account |
| B | Activity date |
| C | Activity type |
Sheet Accounts, one row per assigned account:
| Column | Content |
|---|---|
| A | Account |
| B | Owner |
| C | Tier: A, B or C |
| D | Trailing twelve-month revenue |
Sheet Rules: activity types in A2 down with TRUE or FALSE in B; tiers in D2 down with cadence in days in E, for example A 30, B 90, C 180. As-of date in G1.
Activities!D2:
=IFERROR(VLOOKUP(C2,Rules!$A:$B,2,FALSE),FALSE)
Unknown types default to FALSE. Review them: =COUNTIFS(Activities!D:D,FALSE) against the total shows how much logged activity is not contact.
Accounts!E2:
=MAXIFS(Activities!B:B,Activities!A:A,A2,Activities!D:D,TRUE,Activities!B:B,"<="&Rules!$G$1)
Days since, F2:
=IF(E2=0,"Never",Rules!$G$1-E2)
Cadence, G2:
=VLOOKUP(C2,Rules!$D:$E,2,FALSE)
State, H2:
=IF(F2="Never","Never touched",IF(F2<=G2,"Covered","Overdue"))
On a Calc sheet, reps in A5 down. Assigned, B5:
=COUNTIFS(Accounts!B:B,A5)
Covered, C5:
=COUNTIFS(Accounts!B:B,A5,Accounts!H:H,"Covered")
Coverage by count, D5: =C5/B5. By value, E5:
=SUMIFS(Accounts!D:D,Accounts!B:B,A5,Accounts!H:H,"Covered")/SUMIFS(Accounts!D:D,Accounts!B:B,A5)
A rep whose value coverage is below their count coverage is leaving the large accounts.
Microsoft 365:
=SORT(FILTER(Accounts!A2:H5000,Accounts!H2:H5000<>"Covered"),4,-1)
Older Excel: filter column H and sort by revenue. Account, owner, tier, revenue, last touch, days since, cadence owed. That is Monday's list.
=COUNTIF(Accounts!H:H,"Covered")+COUNTIF(Accounts!H:H,"Overdue")+COUNTIF(Accounts!H:H,"Never touched")-COUNTA(Accounts!A2:A5000)
Zero. And no account assigned twice:
=SUMPRODUCT((COUNTIF(Accounts!A2:A5000,Accounts!A2:A5000)>1)*1)
Also zero.
Account names do not match. The CRM activity says "Acme Ltd" and the account list says "ACME Limited". MAXIFS returns zero and the account reads never touched. Use the account identifier in both exports, not the name.
Every log counts. Without the touch flag, coverage is ninety-five percent by sequence email.
One window for all. A 90-day rule marks most tier-C accounts overdue when they are inside what they are owed.
TODAY() as the as-of date. The list cannot be reproduced.
One snapshot of one team is a fine spreadsheet. Weekly, across teams, with revenue refreshed from the ledger, reassignments tracked and last week's list compared to this week's, is where it strains; see the four signs a spreadsheet is no longer enough. For what the rate should be, see what is a good account coverage rate; for the arithmetic by hand, coverage on a ten-account book. Covirage computes the same list from the same exports every week, with the touch rule stated and the check built in.
Two-way interactions: meetings held, calls connected, emails with a reply, orders taken by a person. Put every activity type in a small table with TRUE or FALSE beside it and look it up. That makes the definition visible and changeable, and stops sequence emails and voicemails inflating coverage.
MAXIFS returns zero, which Excel shows as a date in 1900. Wrap it: if the result is zero, show 'Never' and treat the account as uncovered. Never-touched accounts with revenue are the top of the list, and on a newly assigned book there are usually more than anyone expects.
Because 80 percent by count says nothing about which 80. If value coverage is below count coverage, the uncovered accounts are the large ones. Both figures per rep, side by side, is the read.