Why your Monday numbers should take two minutes
Every Monday morning, sales managers ask the same questions. How many calls did you make? How many meetings did you book? Are you on track for your target? If you are a sales rep, an account executive or a business development rep, you can either spend half an hour counting rows in your CRM export, or you can have a sheet that answers all of it the moment you paste in last week’s data.
This free lesson is the first lab of Excel for Sales Reps: Pipeline, Forecast, Quota & Commission. You play Maya Torres, an account executive at Corvia Label Systems, a fictional supplier of labelling machines. In the lab above you turn one week of logged calls, emails and meetings into the numbers her manager wants: activity counts by type, the number of calls that booked a meeting, the call-to-meeting rate and the comparison with the weekly meeting target. This guide explains every formula, shows how to set it up for your own activity data and warns you about the traps that make activity reports wrong.
You need no advanced Excel skills. If you have ever exported a list from Salesforce, HubSpot, Pipedrive or any other CRM into a spreadsheet, you already have the right kind of data. Everything else is two functions: COUNTIF and COUNTIFS.
The core concept: counting with conditions
An activity log is a list with one row per sales activity: the date, the type of activity (call, email, meeting), the account and the outcome. Most CRMs can export exactly this list. On its own, the list is just a record. Its value comes from counting: how many of each type, how many with a good outcome, how many per account or per day.
COUNTIF counts the cells in a range that meet one condition. It needs only two arguments: the range to look in and the criterion. =COUNTIF($B$2:$B$13,"Call") counts every row whose type is Call. Even better, put the criterion in a cell, F2, and write =COUNTIF($B$2:$B$13,F2). Then the same formula, copied down, counts emails in the next row and meetings in the row after, simply because F3 says Email and F4 says Meeting.
COUNTIFS counts rows that meet several conditions at the same time. Its arguments come in pairs: range 1, criterion 1, range 2, criterion 2, and so on. =COUNTIFS($B$2:$B$13,"Call",$D$2:$D$13,"Meeting booked") counts rows where the type is Call and the outcome is Meeting booked. All ranges must have the same size, and every condition must be true for a row to be counted.
From those counts you get a conversion rate. The call-to-meeting rate is the number of calls that booked a meeting divided by all calls. It tells you something a raw count cannot. If you made 40 calls and booked 2 meetings, the answer is not necessarily "make more calls", it might be "make better calls" to better-qualified accounts. Tracking the rate week by week shows whether a new script, a new list or a new approach is actually working.
Worked example: Maya Torres’s week at Corvia
Here is Maya’s week as it appears in the lab. All accounts and results are fictional, but the layout matches a typical CRM export.
| Row | Date (A) | Type (B) | Account (C) | Outcome (D) |
|---|---|---|---|---|
| 2 | Mon 2 Nov 2026 | Call | Halden Foods | Meeting booked |
| 3 | Mon 2 Nov 2026 | Brixton Logistics | Replied | |
| 4 | Mon 2 Nov 2026 | Call | Orchard Dairy | No answer |
| 5 | Tue 3 Nov 2026 | Meeting | Halden Foods | Next step agreed |
| 6 | Tue 3 Nov 2026 | Call | Kessler Brewing | Meeting booked |
| 7 | Tue 3 Nov 2026 | Orchard Dairy | No reply | |
| 8 | Wed 4 Nov 2026 | Call | Brixton Logistics | Not interested |
| 9 | Wed 4 Nov 2026 | Meeting | Lindqvist Pharma | Next step agreed |
| 10 | Thu 5 Nov 2026 | Call | Pellar Bakeries | Meeting booked |
| 11 | Thu 5 Nov 2026 | Call | Orchard Dairy | No answer |
| 12 | Thu 5 Nov 2026 | Kessler Brewing | Replied | |
| 13 | Fri 6 Nov 2026 | Meeting | Kessler Brewing | No decision |
The summary sits in columns F and G. Labels go into column F, formulas into column G. Notice that the weekly target of 4 meetings sits in its own cell, G9, so that the same sheet works next week, or next quarter when the target changes.
| Cell | Metric | Formula | Result |
|---|---|---|---|
| G2 | Calls | =COUNTIF($B$2:$B$13,F2) | 6 |
| G3 | Emails | =COUNTIF($B$2:$B$13,F3) | 3 |
| G4 | Meetings | =COUNTIF($B$2:$B$13,F4) | 3 |
| G6 | Calls → meeting | =COUNTIFS($B$2:$B$13,"Call",$D$2:$D$13,"Meeting booked") | 3 |
| G7 | Call-to-meeting rate | =G6/G2 | 50% |
| G9 | Meeting target / week | input | 4 |
| G10 | Meetings vs target | =G4-G9 | −1 |
Let’s go through the results. =COUNTIF($B$2:$B$13,F2) finds six rows with Call: rows 2, 4, 6, 8, 10 and 11. Copied down, the same formula finds three emails and three meetings. Together that is twelve activities, which equals the number of rows in the log, a useful check that no type was misspelled.
Next, COUNTIFS counts calls that booked a meeting. Rows 2 (Halden Foods), 6 (Kessler Brewing) and 10 (Pellar Bakeries) meet both conditions, so the answer is 3. A plain =COUNTIF($D$2:$D$13,"Meeting booked") would also give 3 in this example, but only by coincidence: as soon as an email reply leads to a booked meeting, COUNTIF would count it as a call success. COUNTIFS keeps the calls and only the calls.
The rate is =G6/G2 = 3 ÷ 6 = 0.5, which you format as a percentage to show 50%. Finally, =G4-G9 compares the meetings held, 3, with the target of 4, and returns −1: one meeting short. A negative number means below target, a positive number means above.
Note the subtle difference between G4 and G6. G6 counts meetings booked by this week’s calls, which may take place next week. G4 counts meetings held this week, some of which were probably booked the week before. Both numbers matter, but they answer different questions, so label them clearly and do not add them together.
Step by step: build your weekly activity report in Excel
The steps below work in any recent version of Microsoft Excel and, with minor differences, in Google Sheets.
- Export your activities from your CRM as CSV or Excel for the week you want to report. Keep at least the date, type, account and outcome columns, and delete the rest to keep the sheet readable.
- Paste the data into a new sheet starting in A1, with the headers in row 1. Check that the date column is right-aligned; if the dates are left-aligned they are text and need converting with Data › Text to Columns.
- Optional but recommended: click inside the data and press Ctrl + T to turn it into a table, so that next week you can paste more rows and every formula picks them up.
- Leave column E empty as a spacer. In F1:G1 type Activity and Count, and in F2:F4 type Call, Email and Meeting, spelled exactly as in your export.
- In G2 type
=COUNTIF(B2:B13,F2), put the cursor in the range and press F4 to make it$B$2:$B$13, then press Enter. Copy it down to G4 with Ctrl + D. - In F6 type Calls → meeting and in G6 enter
=COUNTIFS($B$2:$B$13,F2,$D$2:$D$13,"Meeting booked"). Using F2 instead of "Call" means the label and the formula can never disagree. - In G7 enter
=G6/G2and press Ctrl + Shift + % to show it as a percentage. If some weeks have no calls, use=IFERROR(G6/G2,0)to avoid a #DIV/0! error. - In G9 type your weekly meeting target, and in G10 enter
=G4-G9. - To highlight a shortfall, select G10 and choose Home › Conditional Formatting › Highlight Cells Rules › Less Than, enter 0 and pick a red fill.
- Save the file as your weekly template. Next Monday, paste the new export over the old data, and the summary updates instantly.
=COUNTIFS($A$2:$A$13,">="&DATE(2026,11,2),$A$2:$A$13,"<="&DATE(2026,11,6),$B$2:$B$13,"Call") counts the calls between 2 and 6 November. The operator goes in quotes and is joined to the date with &.You can also use wildcards in criteria. =COUNTIF($D$2:$D$13,"Meeting*") counts every outcome that starts with "Meeting", and "*Pharma*" would count any account with Pharma in its name. Criteria are not case-sensitive, so "call" and "Call" count the same.
Common mistakes and how to spot them
COUNTIF and COUNTIFS rarely produce error messages. When they are wrong, they are quietly wrong, which is why these checks are worth knowing.
- Inconsistent labels in the export. "Call", "Phone call" and "Call " (with a trailing space) are three different values. Spot it: the three type counts should add up to
=COUNTA(B2:B13). If they do not, filter the column (Ctrl + Shift + L) and look at the list of distinct values. - Using COUNTIF where you need COUNTIFS. Counting all "Meeting booked" outcomes includes meetings booked by email or by a colleague. If the question has two conditions, the formula needs two conditions.
- Ranges of different sizes in COUNTIFS.
=COUNTIFS($B$2:$B$13,"Call",$D$2:$D$12,"Meeting booked")returns #VALUE!. Every range must cover the same rows. - Relative ranges. Without $ signs, copying G2 down shifts the range to B3:B14 and then B4:B15, so rows drop out of the count. Press F4 to lock the range.
- Rates calculated the wrong way round. Calls ÷ meetings gives 2, not 50%. The rate is always the successes divided by the attempts.
- Mixing booked and held meetings. Comparing meetings booked with the target for meetings held makes you look better or worse than you are. Decide which one your target refers to and report that one.
- Dates stored as text. Date conditions in COUNTIFS silently return 0 if the dates are text. Spot it:
=COUNT(A2:A13)should equal the number of rows.
Quick reference: functions and shortcuts in this lesson
| Function / shortcut | What it does | Example from this lesson |
|---|---|---|
COUNTIF | Counts the rows that meet one condition | =COUNTIF($B$2:$B$13,F2) gives 6 calls |
COUNTIFS | Counts the rows that meet all of several conditions | =COUNTIFS($B$2:$B$13,"Call",$D$2:$D$13,"Meeting booked") gives 3 |
COUNTA | Counts non-empty cells | =COUNTA(B2:B13) gives 12 activities |
=G6/G2 | A rate: part divided by whole, formatted as % | Call-to-meeting rate 50% |
COUNTIFS with dates | Counts rows between two dates | =COUNTIFS(A2:A13,">="&DATE(2026,11,2),A2:A13,"<="&DATE(2026,11,6)) |
IFERROR | Replaces an error with a value | =IFERROR(G6/G2,0) in a week without calls |
| F4 | Adds $ signs to the reference under the cursor | $B$2:$B$13 |
| Ctrl + Shift + % | Applies percentage format | G7 shows 50% |
| Ctrl + Shift + L | Turns filters on or off | Show only Orchard Dairy rows |
| Ctrl + T | Turns the log into a table | Formulas keep working as rows are added |

Real-world uses
Counting with conditions is one of the most common jobs in sales operations. Once you know COUNTIFS, you will see uses for it everywhere.
- The Monday one-on-one. Paste last week’s activity export, and the summary gives calls, emails, meetings, the call-to-meeting rate and the gap to target, ready to discuss.
- Account coverage. Count activities per account with
=COUNTIF($C$2:$C$13,"Orchard Dairy")to see which accounts you are neglecting and which ones you call without results. In this example Orchard Dairy got three touches and no reply. - Testing a new call script. Compare the call-to-meeting rate of the weeks before and after a change. A rate tells you more than the number of calls.
- Team reports for managers. Add a Rep column and use COUNTIFS with the rep name as an extra condition to build a small activity table for the whole team.
- Lead source analysis. With a Source column (event, inbound, referral), COUNTIFS shows which source produces the most booked meetings.
What you will build in the rest of this course
This lesson is free. The other nineteen labs are part of the full course, unlocked with a one-time payment of $9.90. They follow Maya through a full sales quarter at Corvia, from the pipeline to her commission statement, and every lab gives you a sheet you can reuse with your own numbers.
Pipeline by Stage and the Weighted Forecast (SUMPRODUCT). You total your open deals by stage and calculate a weighted forecast with SUMPRODUCT, multiplying every deal by the probability of its stage, the number most managers ask for in a forecast call.
Win Rate and Average Deal Size. You count won and lost deals, calculate your win rate correctly (closed deals only, not open ones) and find your average deal size with AVERAGEIF.
Sales Cycle Length: Days from Created to Closed. Dates are numbers in Excel, so the cycle length is simply close date minus created date. You calculate it per deal, average it for won deals and see how long a typical deal really takes.
Quota Attainment and the Gap to Quota. You compare closed revenue with your quota, express attainment as a percentage and calculate the gap you still need to close this quarter, with a clean result even when you are above target.
Tiered Commission: Rates per Tier with MIN and MAX. Tiered plans pay different rates on different slices of revenue. You split revenue into tiers with MIN and MAX and calculate the commission exactly, a sheet you can use to check your own payslip.
Discounts: What They Cost in Margin and in Commission. A 10% discount sounds small. You calculate what it does to the margin of a deal and to your own commission, and see why a small discount can cost much more than it looks.
Ranking Your Accounts: RANK, LARGE and the Top 3. You rank accounts by revenue with RANK.EQ, pull the top three with LARGE and return their names, so you know where to spend your time.
Price Quote Builder: Volume Discounts with XLOOKUP. You build a quote that looks up the right volume discount for any quantity with an approximate-match XLOOKUP, and totals the quote line by line.
Monthly Sales Report: Year-on-Year Growth and a Trend Chart. You compare each month with the same month last year, calculate growth rates and add a line chart that shows the trend at a glance.
Your Sales Dashboard: Attainment by Product, Chart and Colours. Lab 11 puts it all on one screen: attainment by product, a chart and conditional formatting that turns red when a product is behind plan.
Summary
A weekly activity log turns into a useful report with just two functions. COUNTIF counts the rows that meet one condition, such as the type Call. COUNTIFS counts the rows that meet several conditions at once, such as calls whose outcome was Meeting booked. Divide successes by attempts and you get a conversion rate, and a target kept in its own cell turns the counts into a clear gap. Lock the ranges with $ signs, keep the labels consistent, and check that the counts add up to the number of rows.
Now scroll back to the lab and build Maya’s Monday report yourself: count the activities by type, find the calls that booked a meeting, calculate the call-to-meeting rate and compare her meetings with the target. It takes about fifteen minutes, every answer is checked instantly, and you can reuse the sheet with your own CRM export next Monday.
Frequently asked questions
How do I count calls and meetings in Excel?
Use COUNTIF on the activity type column, for example =COUNTIF($B$2:$B$13,"Call"). Put the type in a cell and refer to it, so one formula can be copied for calls, emails and meetings.
What is the difference between COUNTIF and COUNTIFS?
COUNTIF counts rows that meet one condition. COUNTIFS counts rows that meet all of several conditions, for example type Call and outcome Meeting booked.
How do I calculate a call-to-meeting rate in Excel?
Divide the number of calls that booked a meeting by the total number of calls, for example =G6/G2, and format the cell as a percentage. Use IFERROR to avoid #DIV/0! in weeks without calls.
Why does my COUNTIFS return #VALUE!?
All the ranges in a COUNTIFS formula must have the same number of rows and columns. Check that every range starts and ends on the same rows.
Can COUNTIFS count activities between two dates?
Yes. Use two conditions on the date column with comparison operators, for example ">="&DATE(2026,11,2) and "<="&DATE(2026,11,6).
Is COUNTIF case-sensitive?
No. COUNTIF and COUNTIFS treat "call" and "Call" as the same, but extra spaces or different spellings are treated as different values.
Keep going: Excel for Sales Reps: Pipeline, Forecast, Quota & Commission
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.
- Pipeline by Stage and the Weighted Forecast (SUMPRODUCT)
- Win Rate and Average Deal Size
- Sales Cycle Length: Days from Created to Closed
- Quota Attainment and the Gap to Quota
- Tiered Commission: Rates per Tier with MIN and MAX
- Discounts: What They Cost in Margin and in Commission
- Ranking Your Accounts: RANK, LARGE and the Top 3
- Price Quote Builder: Volume Discounts with XLOOKUP
- Monthly Sales Report: Year-on-Year Growth and a Trend Chart
- Your Sales Dashboard: Attainment by Product, Chart and Colours
- Pipeline Hygiene: Past-Due Close Dates, Stale Deals and a Clean Forecast
- Stage Conversion Rates: Win Probabilities from Your Own History
- Working Backwards: Pipeline, Meetings and Calls Needed for the Quota Gap
- Forecast Categories: Commit, Best Case and the Number You Call
- Territory Planning: Wallet Share, Whitespace and Where to Spend Your Visits
- Multi-Year Deals: TCV, ACV, Ramped Pricing and a Capped Kicker
- Commission What-If: Sign the Deal This Quarter or Next?
- Bookings Bridge: Volume and Price Effects vs Last Year
- Final Project: Your Quarterly Business Review Pack