To get average deal size by segment, average only closed-won deals with AVERAGEIFS, then check the export before you report the number. You pull the export from your CRM (the software where your sales team tracks deals), open it in a spreadsheet, and type a formula called AVERAGEIFS. It averages only the rows that pass the tests you give it, such as "the segment is Enterprise." The answer looks clean. It can still be wrong, and the formula will not warn you.
Key takeaways
- Settle the rules first: use closed-won deals only, over a fixed period.
- Confirm the export holds every deal by comparing its count with your CRM.
- Make every range the same length, and keep amounts stored as numbers.
- Clean segment names with TRIM, and count blank segments before you report.
Sales leaders use these numbers to set targets, price deals, and decide where to put effort. A wrong average can point a team at the wrong segment for a long time, so the checks are worth doing every time.
This post covers four common mistakes. Each one can leave a number that looks fine but is not.
Every formula here assumes the same layout: one row for each deal, the header in row 1, and all deals listed below it. Column B holds the segment, column C the status and column D the amount. In the formulas, replace <last> with the row number of your final deal, so D2:D<last> covers the whole amount column. Change the letters to match your own export.
Decide what counts as a deal first
Before any formula, settle the rules of the report. For deal size, use closed-won deals only, over a fixed period such as the last quarter (see open, won and lost stage rules). If open deals are mixed in, the average includes prices that have not been agreed yet, and the number moves every time someone updates a deal.
Put those rules into the formula as tests, so they are written down instead of remembered. Each test is a pair: a column to look in, then the answer you want to find there.
=AVERAGEIFS(D2:D<last>, B2:B<last>, "Enterprise", C2:C<last>, "Won")
In words, it reads: take the amounts where the segment is Enterprise and the status is Won, and average them.
Check the export has every deal
Make sure the export holds every deal you expect before you trust any average (see checks for weekly pipeline exports). A formula can only average the rows it receives. Some CRMs limit what each user can see. Pipedrive, for example, lets you sort users into visibility groups and decide which items they can view. If the person who pulled the export has narrow access, some deals may never reach your spreadsheet, and your averages will be built on a partial list. A missing deal changes its segment's average, and nobody can tell from the number alone.
Quick test: filter your CRM to the same segment and dates, count the deals on screen, then compare that number with the count in your spreadsheet. If they do not match, fix the export before you touch the formula. If you are not sure, ask whoever runs the CRM which users have limited access before you pull the export.
Build the segment table once
Once the export is clean, build one small table: one row for each segment, with the deal count and the average beside it. Put the segment names in column F, count them in column G and average them in column H.
G2: =COUNTIFS($B$2:$B$<last>, F2, $C$2:$C$<last>, "Won")
H2: =AVERAGEIFS($D$2:$D$<last>, $B$2:$B$<last>, F2, $C$2:$C$<last>, "Won")
Fill both formulas down, so each segment gets its own row. Type the segment names exactly as they appear in the export, including spelling and spacing. Then run the checks below before you share the table.
Mistake 1: the columns don't line up
The formula works in matched pairs. Every column you test has to be the same length as the column of amounts. Microsoft's rule is strict: each criteria range must be the same size and shape as the average range. In plain words, a test column is a criteria range, and the column of amounts is the average range.
A common way to break it looks like this:
=AVERAGEIFS(D2:D<last-1>, B2:B<last>, "Enterprise")
The amounts stop one row short, but the segment column runs all the way down. This often happens after new deals are added to the bottom of the export and only one column gets stretched. It also happens when one range starts on the header row and the other starts one row lower. The two columns no longer line up row by row, so the pairing is broken.
Click into the formula and compare the first and last row of each range. If one ends a row early or late, the ranges do not match, and you should fix it before you trust the number.
The mistake hides easily. New rows get added to exports, and it is easy to stretch one column and forget the other.
Fix it in one of two ways. The first is to check that the first row and last row match in every range, then correct the one that is off. The second, and safer, is to turn the export into an Excel table. Click inside the data and convert it into an Excel table, confirming that the table has headers. Then rename the table to Deals on the Table Design tab.
Then write the test with the column names:
=AVERAGEIFS(Deals[Amount], Deals[Segment], "Enterprise")
This works if your columns are named Amount and Segment. Named table columns make it easier to keep the ranges aligned.
Mistake 2: amounts stored as text
The formula can only average numbers. Exports often bring money in as text, with dollar signs, commas or stray spaces. The cell looks like a number, but Excel treats it as text. When a cell in the amount column cannot be turned into a number, the formula cannot produce an average.
Scroll down the amount column. Text amounts often look different from the rest of the column, so look for any cell that does not match. Text amounts do not show up as zeros or blanks. They look like money, which is why they are easy to overlook until a formula fails.
To find the text amounts, use a spare column. Place this ISNUMBER test beside the first amount, then fill it down every row:
=ISNUMBER(D2)
TRUE means Excel reads the cell as a number. FALSE means it is text, and the average will fail until you fix that cell.
To convert a whole column at once, use your spreadsheet's conversion tool on that column. If a few cells stay as text afterward, retype their amounts by hand. If the export keeps bringing in text, check its settings before you convert anything. Some CRMs let you choose how amounts are formatted when you export, so pick the option that leaves out dollar signs and commas.
Watch for this one. Text amounts can hide among numbers, so the check above should cover every row, not just the first few.
Mistake 3: wildcards in segment names
Two symbols in a test mean something other than what they look like. The question mark (?) matches any single character, and the asterisk (*) matches any run of characters. So a test for "Mid*" does not mean the literal word Mid followed by a star. If your list also has Midwest and Mid-Atlantic, that test matches all three, and your Mid-Market average quietly picks up their deals.
The formula below causes it:
=AVERAGEIFS(D2:D<last>, B2:B<last>, "Mid*")
Some segment names really do contain one of these symbols, such as Retail? or Q4*. In that case, put a tilde (~) in front of the symbol, so the test reads "Retail~?". Microsoft says a tilde tells Excel to match the actual character.
This mistake gives a number that looks right, so check what the test actually matched. Run the same test with COUNTIF:
=COUNTIF(B2:B<last>, "Mid*")
If the count includes segments you did not mean, make the test narrower.
Scan the segment list for any name that contains a question mark or an asterisk. Then check every test that uses an asterisk on purpose. Those are the ones that can match more rows than you meant.
Wildcards look like punctuation, so they are easy to read past. A dash or a slash in a segment name is safe. Only the question mark and asterisk act as wildcards.
Spaces cause a quieter problem. Clean the column first. Type =TRIM(B2) in a spare column, copy it down, and point the formula at that clean column instead.
Two names can look identical and still differ. "Mid-Market" with a normal hyphen and "Mid–Market" with a longer dash look the same on screen, but they are different text, so they land in different averages.
Mistake 4: blank segments disappear
This is the quietest mistake. Microsoft's rule is that if a cell in a criteria range is empty, AVERAGEIFS treats it as a 0 value. A deal with a blank segment therefore never counts toward any named segment. It does not match Enterprise, Mid-Market or any other label, and the formula does not tell you it was skipped.
Imagine that a handful of your deals have no segment because the field was left empty when reps entered them. Each segment average still prints a number, calculated from the deals that do have a segment, so every number looks normal. The missing deals are simply absent from every answer.
Sort a copy of the export by the segment column in ascending order. On your copy, check whether the blank cells group together, so they are easy to find and count.
The missing deals never show an error message. They are just absent, so the report looks finished. For more on this, see checks for missing rows in forecast extracts.
Count the blank cells before you average. Enter this in a spare cell:
=COUNTBLANK(B2:B<last>)
If the answer is more than zero, find out what those deals are. Ask the rep, or look up the company in the CRM, and fill in the segment. Do not guess. A wrong guess moves a deal into the wrong average, which can be just as misleading as leaving it out.
Illustrative example: one blank segment
Suppose the Enterprise group has three large deals and the Mid-Market group has two smaller ones. A third deal has no segment at all, and it is far larger than the rest. Each segment average looks reasonable without it. Yet the blank deal still belongs in the company total. If it turns out to be an Enterprise deal, that segment's average rises sharply, because one large deal moves an average a lot. That is why a blank cell is worth chasing down.
Averaging the segment averages would also give the two-deal Mid-Market group the same weight as the three-deal Enterprise group, which is wrong. Averaging every deal, including the blank one once it is labeled, matches what the report is meant to measure.
Do not average the averages
If you also need one company-wide average, do not average the segment averages. A segment with a few deals would count as much as a segment with hundreds, which skews the result. Use a single formula over all the deals instead:
=AVERAGEIFS(D2:D<last>, C2:C<last>, "Won")
Each deal then counts once, which is what an average deal size should measure.
Four checks before you report
Run these checks on every export, in this order:
- Compare the deal count in your spreadsheet with the count on screen in your CRM, using the same filters.
- Check every amount: the ISNUMBER test should say TRUE on every row, with no FALSE.
- Count the blank segments with the COUNTBLANK formula from Mistake 4. It should show 0.
- Count each segment with
=COUNTIFS(B2:B<last>, "Enterprise"), add the counts up, and compare with the total number of deals. The two numbers must match.
A good result is simple: every amount reads TRUE, no segment is blank, and the segment counts add up to the total.
If any check fails, fix the export first, then run the formulas again. Do not report a number that failed a check. A clean-looking number that is wrong is the expensive kind, because people act on it.
Your next step
Today, open your current export and compare its deal count with the count on your CRM screen, using the same filters. That one comparison shows whether any deals are missing before you trust an average.
Frequently asked questions
Can I just delete the blank deals?
Only if you know they are not real deals, such as test records or duplicates. A deal that is real but unlabeled still belongs in the company total, so the right move is to label it, not delete it.
The short version
Four mistakes, one habit. Match your columns, keep amounts as numbers, use clean segment names, and never leave a segment blank. Keep the checks in the same spreadsheet tab as the report, and run them every time you pull a new export. Then the number your leader sees is one you can stand behind.
If you only remember one thing, remember this: a clean-looking average is not the same as a correct one. Test the inputs first, then trust the output.