Tabellab

ICP Fit Scoring: Industry Points, Size Bands, a Capped Site Score and the Target List

Lesson 1 of 20Lab

All lessons
  1. ICP Fit Scoring: Industry Points, Size Bands, a Capped Site Score and the Target ListLabFree
  2. Target Account Tiers: One-to-One, One-to-Few and One-to-Many with AND, COUNTIFS and a Tier BudgetLab Locked
  3. Weighted Fit Model: SUMPRODUCT Scores, RANK.EQ and a Capacity Cut-Off with LARGELab Locked
  4. Account Engagement Score: Weighted Activities, Hot-Warm-Cold Status and Score per Active ContactLab Locked
  5. Engagement Recency: a 30-Day Window with SUMIFS and Half-Life Decay with SUMPRODUCTLab Locked
  6. Buying-Committee Coverage: Engaged Contacts, Roles Covered and the Single-Threaded RiskLab Locked
  7. Account Stage Progression: Stage Numbers with XLOOKUP, Forward-Held-Back and the Progression RateLab Locked
  8. Pipeline Velocity: Opportunities, Win Rate, ACV and Cycle Length by ProgrammeLab Locked
  9. ABM vs Broad Demand Gen: ROI per Programme, SUMIFS and SUMPRODUCT Group TotalsLab Locked
  10. Account Funnel: Step Rates, ABM Advantage, the Weakest Step with INDEX-MATCH and a Formula RuleLab Locked
  11. Win Rate and ACV by Tier: COUNTIFS, AVERAGEIFS and the Expected Value of a DealLab Locked
  12. Sales Cycle Length: Date Differences, AVERAGEIFS, MAXIFS and the Share of Fast ClosesLab Locked
  13. ABM Budget Allocation: Shares by Tier, SUMPRODUCT Tier Costs and Cost per Account, Engaged Account and OpportunityLab Locked
  14. ABM Play Performance: Advance Rate, Cost per Advanced Account, RANK.EQ and a Keep-or-Review RuleLab Locked
  15. Intent Signals: Precision, Recall and Lift for Three Score ThresholdsLab Locked
  16. ABM Pipeline Forecast: SLOPE, RSQ and FORECAST.LINEAR for the Next QuarterLab Locked
  17. Expansion ABM: Whitespace, Net and Gross Revenue Retention with SUMPRODUCT and INDEX-MATCHLab Locked
  18. Account Attribution: Linear and U-Shaped Credit per Touch and SUMIFS Channel TotalsLab Locked
  19. Budget Mix Scenarios: a Two-Way Grid with Mixed References, a Required Share and a Heat MapLab Locked
  20. Final Project: an ABM Account Readiness Dashboard with Weights, Next Actions, Tier Summaries, Chart and Heat MapLab Locked

You are not signed in — the lab works, but your progress is not saved. Sign up

Lesson 1 of 20 is free. Unlock all 20 hands-on labs, the final exam and your certificate.

Buy course — $9.90
Loading the spreadsheet…

Why start with an ideal customer profile?

Most marketing teams are rewarded for volume: more leads, more clicks, more form fills. In business-to-business sales that logic breaks down quickly. A company that sells loading-dock equipment, freight software or industrial services does not need ten thousand anonymous contacts. It needs a few hundred companies that are large enough, in the right industry and ready to talk, and it needs the right people at each of them to hear from the business again and again. That is the idea behind account-based marketing (ABM): you decide which accounts you want first and only then plan the marketing for them.

The first decision is the hardest, because it is usually made by gut feeling. Sales says the big logistics firms are the best customers, marketing points to a few small food producers that signed fast, and a long list of companies in between gets nothing but a newsletter. An ideal customer profile (ICP) replaces that debate with a short written description of the companies that buy quickly, stay long and pay well, expressed in facts you can look up: industry, size, number of sites, the tools they already use.

An ICP on a slide is not yet a tool. To use it you need to turn it into a number for every candidate company, so that a list of five hundred names can be sorted in seconds and the discussion moves from opinions to weights. This lesson builds exactly that number, the ICP fit score, in a spreadsheet, and the next 19 lessons build everything an ABM programme needs on top of it.

What an ICP fit score is

A fit score awards points for each criterion of the profile and adds them up. The maximum is fixed in advance (100 is convenient), each criterion gets a share of it, and a threshold separates the accounts that deserve ABM attention from the rest. The score answers one question only: does this company look like the customers we serve best? It says nothing about whether the company is interested today. Interest is measured separately, with the engagement score in lessons 4 and 5.

  • Industry (0 to 40 points). Industries differ in how well the product fits. A table of industries and points is read with a lookup function.
  • Company size (5, 20 or 30 points). Three employee bands: from 1,000 employees, from 250 employees and smaller. Bands are a nested IF.
  • Number of sites (2 points each, at most 20). More sites mean more docks, trucks or users. The cap stops one huge account from dominating the scale.
  • Legacy tool (0 or 10 points). A company that still runs the old scheduling tool has a reason to switch.
Score companies on firmographic and behavioural facts only: industry, size, sites, tools, activity at work. Never score or target people by personal characteristics such as age, gender, origin or health.

The example: twelve candidate accounts at Brindlemoor Dockware

Brindlemoor Dockware is a fictional supplier of loading-dock equipment and scheduling software. All companies, people and numbers in this article and in the course are invented for teaching. Its sales team has handed marketing a list of twelve candidate accounts. The scoring rules are: industry points from a table (logistics 40, retail 30, food and beverage 25, manufacturing 20, other 5); size points of 30 from 1,000 employees, 20 from 250 employees and 5 below; 2 points per site, capped at 20; 10 points when the account still uses the legacy scheduling tool. An account is a Target from 70 points and otherwise stays in Nurture.

Here is the list with every component of the score. The sheet in the lab holds the first five columns and a rule table; the points and the fit score are what you calculate.

AccountIndustryEmployeesSitesIndustry ptsSize ptsSite ptsLegacy bonusFit scoreStatus
Aldersey FreightLogistics2,4001440302010100Target
Brackwell FoodsFood & beverage8606252012057Nurture
Calderpoint RetailRetail5,200223030201090Target
Dunmore LogisticsLogistics310440208068Nurture
Eskfield PharmaOther1,50035306041Nurture
Fennhurst MotorsManufacturing3,10092030181078Target
Glenrow Cold ChainLogistics64084020161086Target
Harlowe HomeRetail18023054039Nurture
Inchmarlo ParcelLogistics1,25011403020090Target
Jessamy BeveragesFood & beverage42052520101065Nurture
Lowmoor SteelManufacturing950320206046Nurture
Marrable GrocersRetail1,900123030201090Target

The formulas, written for row 4 and copied down to row 15, are:

  • Industry points (F4): =XLOOKUP(B4,$K$4:$K$8,$L$4:$L$8). It finds the industry in K4:K8 and returns its points from L4:L8.
  • Size points (G4): =IF(C4>=$K$11,$L$11,IF(C4>=$K$12,$L$12,$L$13)). The highest band is tested first.
  • Fit score (H4): =F4+G4+MIN(D4*$L$15,$L$16)+IF(E4="Yes",$L$17,0). Industry plus size plus the capped site points plus the bonus.
  • Status (I4): =IF(H4>=$L$18,"Target","Nurture"). The threshold sits in L18.

Walk through a few rows. Aldersey Freight is a logistics company with 2,400 employees, 14 sites and the legacy tool: 40 + 30 + 20 (14 sites would earn 28, the cap stops at 20) + 10 = 100, the maximum. Inchmarlo Parcel is also logistics, has 1,250 employees and 11 sites but no legacy tool: 40 + 30 + 20 (11 sites would earn 22, capped at 20) + 0 = 90. Harlowe Home, a small retailer with 180 employees and 2 sites, reaches only 39 points.

ICP fit score vs the target thresholdTarget (70 or more)NurtureThreshold 70100Aldersey Freight90Calderpoint Retail90Inchmarlo Parcel90Marrable Grocers86Glenrow Cold Chain78Fennhurst Motors68Dunmore Logistics65Jessamy Beverages57Brackwell Foods46Lowmoor Steel41Eskfield Pharma39Harlowe Home020406080100Fit score out of 100 (fictional accounts)
Figure 1: Fit scores of the twelve candidate accounts. The dashed line is the target threshold of 70 points; the six green bars are the Target accounts, the six peach bars stay in Nurture.

The result: 6 of the 12 accounts are Targets, and the other 6 stay in Nurture. Nurture does not mean ignored; it means broad, low-cost marketing instead of a dedicated plan. Dunmore Logistics deserves a closer look: with 68 points it misses the threshold by 2 points, and a small change in one rule could move it. Eskfield Pharma shows the opposite: it scores 5 points for industry but 30 for size, so its total of 41 is far from the threshold.

StatusAccountsShareFormula
Target650%=COUNTIF($I$4:$I$15,"Target")
Nurture650%=COUNTIF($I$4:$I$15,"Nurture")
Total12100%=COUNTA($A$4:$A$15)

The building blocks, one function at a time

XLOOKUP takes a value, finds it in a column and returns the matching entry from another column. Here the value is the industry of the account. Because the point table is a small range of its own, you can change the weight of an industry in one cell and all twelve scores update. INDEX with MATCH, or VLOOKUP, would also work; XLOOKUP is simply the easiest to read.

Nested IF handles the size bands. Order matters: test the highest band first. If you test C4>=250 first, every large company stops at the 20-point band and never reaches the 1,000-employee test. A company with 5,200 employees would then score 20 for size instead of 30.

MIN implements the cap. MIN(D4*2,20) returns the smaller of the two numbers, so 14 sites give 20 points, not 28. Without the cap, a company with 40 sites would earn 80 points on this criterion alone and the whole scale would be distorted.

IF with a text test awards the bonus: IF(E4="Yes",10,0). In the lab the bonus is stored in a cell, so the formula points to $L$17 and nobody has to edit formulas when the weight changes.

Keep every point value and the threshold in cells next to the data, never inside formulas. Sales will challenge the weights, and you want to change one cell, not twelve formulas.

From profile to list: how the fit score worksIndustry0 to 40 pointsXLOOKUP in a tableSize5, 20 or 30 pointsnested IF, top band firstSites2 points each, max 20MIN(sites × 2, 20)Legacy tool0 or 10 pointsIF(… = "Yes", 10, 0)Fit scoresum, max 100Score≥ 70?TargetNurtureAll point values and the threshold sit in cells, not in formulas
Figure 2: The scoring logic of lesson 1. Each criterion turns a company fact into points, the points add up to a fit score and a threshold in a cell decides Target or Nurture.

Step by step in a spreadsheet

  1. Enter the accounts with industry, employees, sites and the legacy-tool flag in A3:E15 and the rule tables to the right (industry points in K4:L8, size bands in K11:L13, site points, cap and bonus in L15:L17, threshold in L18).
  2. In F4 write the XLOOKUP for the industry points and copy it down to F15.
  3. In G4 write the nested IF for the size points, highest band first, and copy it down to G15.
  4. In H4 add industry points, size points, the capped site points and the bonus, and copy it down to H15.
  5. In I4 compare the fit score with the threshold: =IF(H4>=$L$18,"Target","Nurture"). Copy it down.
  6. Sort a copy of the list by fit score, largest first, and read the top accounts with the sales team. Ask whether anything surprises them.

Common mistakes and how to spot them

  • Wrong band order. Testing the lowest band first gives every large account the same size points. If five very different companies show identical size points, check the order.
  • No cap on the site points. Without MIN, one account with many sites gets a score above 100 or distorts the sort order. Check that the maximum score equals your design maximum.
  • Unlocked lookup table. XLOOKUP(B4,K4:K8,L4:L8) copied down becomes K5:K9, then K6:K10 and #N/A appears. Lock the table with dollar signs: $K$4:$K$8.
  • Typed threshold. A formula with the number 70 inside cannot be reviewed. Keep it in a cell and refer to it.
  • Using > instead of >=. The rule says the account is a Target from 70 points. A score of exactly 70 belongs on the list.
  • Treating the score as the decision. The score is an input. Keep a way for sales to nominate an account with a written reason.

How to read and challenge the result

A score is only as good as the profile behind it. Test it before you use it. Take your last twenty won deals and your last twenty lost deals and score them with the same rules. If the won deals do not score clearly higher on average, the weights are not describing your best customers, and the criteria or the points need work. In the same way you can check whether a criterion matters at all: remove it and see whether the ranking changes. Criteria that never change the ranking only add work.

Look at the edges of the list. The accounts just below the threshold are the cheapest to review by hand: a two-point gap is not a verdict. And look at the accounts far above it. If all of them are in a single industry, you have built a list of one segment, which may be right or may mean that the industry table is too generous. Finally, remember what the fit score cannot see: timing. A perfect-fit account that has never visited your site and a mediocre-fit account that has asked for a demo need different plans, which is why the course adds engagement, committee coverage and stage next.

Scores support the decision about where to spend effort; they do not replace judgement. Document the rules, the date of the last change and who agreed them.

Quick reference: functions in this lesson

FunctionWhat it does hereExample
XLOOKUPIndustry points from the rule table=XLOOKUP(B4,$K$4:$K$8,$L$4:$L$8)
IF (nested)Size band, highest band first=IF(C4>=$K$11,$L$11,IF(C4>=$K$12,$L$12,$L$13))
MINCap on the site points=MIN(D4*$L$15,$L$16)
IF with textLegacy tool bonus=IF(E4="Yes",$L$17,0)
COUNTIFNumber of Target accounts=COUNTIF($I$4:$I$15,"Target")
Absolute referenceLock the rule tables when copying$K$4:$K$8

Real-world uses

  • Building the target account list. Score a purchased or exported list of companies and keep the top slice for dedicated plans.
  • Agreeing priorities with sales. Show the weights and ask sales to change them. The argument moves from names to rules.
  • Territory and capacity planning. The number of accounts above the threshold tells you how many one-to-one plans the team can afford, which is the topic of lesson 2.
  • Checking the funnel. Compare the fit score of the leads that arrive with the score of your target list. Many high-volume sources attract low-fit companies.
  • Reviewing the profile. Once a quarter, re-score customers and update the weights for the industries that actually performed.

What you will build in the rest of this course

Lesson 1 is free. Lessons 2 to 20 make up the paid course ($9.90 one-time). Each has its own live lab with the same fictional company, and they build from the fit score to a complete readiness dashboard.

  • Lesson 2, Target Account Tiers: You assign fifteen scored accounts to three tiers with IF and AND, count each tier with COUNTIFS and cost the programme with SUMPRODUCT.
  • Lesson 3, Weighted Fit Model: You build a weighted fit score with SUMPRODUCT, rank twelve accounts, find the cut-off score for a capacity of five accounts with LARGE and chart the result.
  • Lesson 4, Account Engagement Score: You roll five activity types up into an account engagement score with SUMPRODUCT, set a Hot, Warm or Cold status and compare depth with breadth through the score per active contact.
  • Lesson 5, Engagement Recency: You date an activity log, add up points per account for all time and for a 30-day window, and build a half-life decayed score that shows who is engaged now.
  • Lesson 6, Buying-Committee Coverage: You count engaged contacts and engaged roles per account with COUNTIFS, turn them into a coverage percentage and flag single-threaded accounts and missing budget owners.
  • Lesson 7, Account Stage Progression: You translate stage names into numbers with XLOOKUP, classify sixteen accounts as Forward, Held or Back, calculate the progression rate with COUNTIF and highlight the setbacks.
  • Lesson 8, Pipeline Velocity: You calculate pipeline velocity for four programmes, test the effect of a shorter sales cycle, work out each segment's share of the total and chart the velocity.
  • Lesson 9, ABM vs Broad Demand Gen: You calculate the ROI of five programmes from cost, revenue and margin, roll them up into ABM and broad groups with SUMIFS and SUMPRODUCT and chart the programme ROIs.
  • Lesson 10, Account Funnel: You calculate step rates for the ABM list and a broad audience, divide them into an advantage multiple, find the weakest stage with INDEX and MATCH and flag the stages where ABM trails.
  • Lesson 11, Win Rate and ACV by Tier: You count closed deals per programme, calculate win rates and the average ACV of won deals with COUNTIFS and AVERAGEIFS, and multiply them into an expected value per deal.
  • Lesson 12, Sales Cycle Length: You turn creation and closing dates into cycle days, summarise the cycles per programme with AVERAGEIFS and MAXIFS, calculate the share of fast closes and chart the averages.
  • Lesson 13, ABM Budget Allocation: You check the tier shares of six budget lines, allocate the budget to three tiers with SUMPRODUCT and calculate the cost per account, per engaged account and per opportunity.
  • Lesson 14, ABM Play Performance: You compare eight ABM plays by advance rate and cost per advanced account, rank them with RANK.EQ, label the best five Keep and chart the costs.
  • Lesson 15, Intent Signals: You count flagged accounts and hits at three intent-score thresholds with COUNTIFS and calculate precision, recall and lift to see how well the signal predicts opportunities.
  • Lesson 16, ABM Pipeline Forecast: You fit a linear trend to twelve months of ABM-sourced pipeline with SLOPE, RSQ and FORECAST.LINEAR, forecast the next three months and chart actual against trend.
  • Lesson 17, Expansion ABM: You size the whitespace of ten customer accounts, calculate net and gross revenue retention and expansion ARR with SUMPRODUCT, and find the account with the largest whitespace.
  • Lesson 18, Account Attribution: You share the value of five won deals over their touches with a linear and a U-shaped model, add the credit up per channel with SUMIFS and compare the two models in a chart.
  • Lesson 19, Budget Mix Scenarios: You build a scenario grid of ABM share against pipeline per euro with mixed references, measure the gain over the base case, solve for the share that reaches a target and shade the grid.
  • Lesson 20, Final Project: You combine fit, engagement, committee coverage and stage into a readiness score, assign a next action to twelve accounts, summarise the tiers with AVERAGEIFS and SUMIFS, and finish with a chart and a heat map.

Summary

An ideal customer profile becomes useful when it is a number. Industry points from a lookup table, size points from a nested IF with the highest band first, site points capped with MIN and a bonus for the legacy tool add up to a fit score out of 100; with a threshold of 70 points 6 of the 12 fictional accounts become Targets. Keep all weights in cells, challenge the profile with won and lost deals, and use the score to decide where to spend effort, not to replace the conversation with sales.

Now scroll up to the lab and build the sheet yourself. It takes about fifteen minutes, and every cell is checked as you type.

Frequently asked questions

What is an ideal customer profile (ICP) in account-based marketing?

An ICP is a short, written description of the companies that buy quickly, stay long and pay well, expressed as facts you can look up: industry, size, number of sites, tools in use. In ABM it defines which accounts earn dedicated attention. It describes companies, not individual people.

How do I calculate an ICP fit score in Excel?

Give each criterion a maximum number of points, look up the points with XLOOKUP, IF or MIN, and add them up. For example =XLOOKUP(B4,$K$4:$K$8,$L$4:$L$8)+size points+MIN(D4*2,20)+IF(E4="Yes",10,0). Keep all point values in cells so the weights can be changed without editing formulas.

What threshold should I use for target accounts?

There is no universal number. Choose a threshold that gives you as many accounts as your team can serve, then check it by scoring past won and lost deals: won deals should score clearly higher. Review the accounts just below the line by hand, because a gap of a few points is not a verdict.

Why do I test the highest size band first in a nested IF?

IF stops at the first true condition. If you test C4>=250 before C4>=1000, every company with 250 or more employees stops at the 250 band and the 1,000 band is never reached. Test from the highest threshold down, or use IFS in the same order.

What is the difference between fit and engagement?

Fit says whether a company looks like your best customers. Engagement says whether people at that company are paying attention now. A strong programme uses both: high fit with low engagement needs outreach, while low fit with high engagement needs a second look before sales time is spent.

Is it acceptable to score contacts on personal characteristics?

No. Score companies on firmographic facts and people on professional behaviour such as what they read or attend. Do not use personal characteristics such as age, gender, origin or health for targeting, and keep to the consent rules for email and messaging in your region.

Keep going: Account-Based Marketing: ICP Scoring, Target Account Tiers, Engagement Scores, Buying-Committee Coverage, Pipeline Velocity & ABM vs Demand Gen ROI

This free lab is lesson 1 of 20. Unlock the other 19 hands-on labs, the final exam and your certificate for a one-time $9.90.

  1. Target Account Tiers: One-to-One, One-to-Few and One-to-Many with AND, COUNTIFS and a Tier Budget
  2. Weighted Fit Model: SUMPRODUCT Scores, RANK.EQ and a Capacity Cut-Off with LARGE
  3. Account Engagement Score: Weighted Activities, Hot-Warm-Cold Status and Score per Active Contact
  4. Engagement Recency: a 30-Day Window with SUMIFS and Half-Life Decay with SUMPRODUCT
  5. Buying-Committee Coverage: Engaged Contacts, Roles Covered and the Single-Threaded Risk
  6. Account Stage Progression: Stage Numbers with XLOOKUP, Forward-Held-Back and the Progression Rate
  7. Pipeline Velocity: Opportunities, Win Rate, ACV and Cycle Length by Programme
  8. ABM vs Broad Demand Gen: ROI per Programme, SUMIFS and SUMPRODUCT Group Totals
  9. Account Funnel: Step Rates, ABM Advantage, the Weakest Step with INDEX-MATCH and a Formula Rule
  10. Win Rate and ACV by Tier: COUNTIFS, AVERAGEIFS and the Expected Value of a Deal
  11. Sales Cycle Length: Date Differences, AVERAGEIFS, MAXIFS and the Share of Fast Closes
  12. ABM Budget Allocation: Shares by Tier, SUMPRODUCT Tier Costs and Cost per Account, Engaged Account and Opportunity
  13. ABM Play Performance: Advance Rate, Cost per Advanced Account, RANK.EQ and a Keep-or-Review Rule
  14. Intent Signals: Precision, Recall and Lift for Three Score Thresholds
  15. ABM Pipeline Forecast: SLOPE, RSQ and FORECAST.LINEAR for the Next Quarter
  16. Expansion ABM: Whitespace, Net and Gross Revenue Retention with SUMPRODUCT and INDEX-MATCH
  17. Account Attribution: Linear and U-Shaped Credit per Touch and SUMIFS Channel Totals
  18. Budget Mix Scenarios: a Two-Way Grid with Mixed References, a Required Share and a Heat Map
  19. Final Project: an ABM Account Readiness Dashboard with Weights, Next Actions, Tier Summaries, Chart and Heat Map

Buy course — $9.90 See the full course