Excel guide · 4 min read
How to calculate CSAT score in Excel
=COUNTIFS(D2:D13,">=4")/COUNT(D2:D13). COUNT only counts numeric ratings, so blanks and "N/A" answers stay out of both the top and the bottom.Sample data
Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.
| Row | A | B | C | D | E | F |
|---|---|---|---|---|---|---|
| 1 | Ticket | Agent | Channel | Rating (1-5) | Tickets closed | |
| 2 | T-1041 | Priya | Chat | 5 | 40 | |
| 3 | T-1042 | Marcus | 4 | |||
| 4 | T-1043 | Priya | Phone | 5 | Min responses | |
| 5 | T-1044 | Jo | 3 | 4 | ||
| 6 | T-1045 | Marcus | Chat | N/A | ||
| 7 | T-1046 | Priya | Chat | 5 | ||
| 8 | T-1047 | Jo | Phone | 2 | ||
| 9 | T-1048 | Marcus | 4 | |||
| 10 | T-1049 | Jo | Chat | |||
| 11 | T-1050 | Priya | 4 | |||
| 12 | T-1051 | Marcus | Phone | 1 | ||
| 13 | T-1052 | Jo | Chat | 5 |
Download this sheet as an Excel practice file (.xlsx)
CSAT = satisfied responses / all responses
The standard CSAT score counts the top two answers on a 1-5 scale (4 = satisfied, 5 = very satisfied) and divides by the number of people who answered. COUNTIFS(D2:D13,">=4") counts the 4s and 5s: 7 here. COUNT(D2:D13) counts the numeric ratings: 10. CSAT is 70%.
If your survey uses a different scale, change the cutoff and say so on the report. On a 1-10 scale, teams often count 8 and above; on a 1-3 scale, only the top answer. Some teams count only 5s, which here would give 40%. Pick one definition and keep it, or month-to-month comparisons mean nothing.
Result: 0.7 shows as 70% with Percent format
0.7: 7 of the 10 people who gave a rating chose 4 or 5, so CSAT is 70%.
Why the average rating and CSAT tell different stories
The average of the same ratings is 3.8. That's not a different measurement of satisfaction, it's a different question: the average blends everyone into one number, while CSAT asks how many people were happy. A 1 and a 5 average to 3, which looks fine, while CSAT counts one happy customer and one very unhappy one.
Report CSAT as the headline (it's what most benchmarks and tools use) and keep the average or the full count of each rating next to it. The share of 1s and 2s, =COUNTIFS(D2:D13,"<=2")/COUNT(D2:D13), is 20% here, and those are the tickets worth reading first: =FILTER(A2:A13,(D2:D13<=2)*ISNUMBER(D2:D13)) lists T-1047 and T-1051.
Result: 3.8
3.8: the average rating, from the same 10 answers that give a CSAT of 70%.
Keep N/A and blank answers out of the score
Survey exports are messy: T-1045 says N/A and T-1049 is blank. Neither person rated the service, so neither belongs in the score. COUNT and COUNTIFS with a number criterion both skip text and empty cells, which is why the formula above already handles them.
The common mistakes divide by the wrong count. COUNTA counts the N/A as a response and gives 7/11 = 63.6%. Dividing by all 12 rows gives 58.3%. And a comparison like --(D2:D13>=4) inside SUMPRODUCT counts the N/A as satisfied, because Excel ranks any text above any number in comparisons: it finds 8 satisfied instead of 7.
One more trap: ratings exported as text ("4" with a green triangle) are skipped by COUNT and COUNTIFS, so the score is calculated on fewer answers than you think. If COUNT is lower than the number of ratings you can see, convert the column with Data > Text to Columns > Finish.
Result: 0.63636363636 shows as 63.6% with Percent format
0.636: the wrong answer. COUNTA counts the N/A as a response, so CSAT drops from 70% to 63.6%.
Response rate
CSAT only describes the people who answered. Response rate shows how many that is: numeric responses / tickets closed in the same period. Here 10 ratings from 40 closed tickets is 25%.
A low response rate doesn't make CSAT wrong, but it makes it fragile: unhappy customers are often more likely to answer. Show the response rate next to CSAT on any dashboard, and keep both on the same date range, by when the ticket closed rather than when the survey came back.
Result: 0.25 shows as 25% with Percent format
0.25: 10 rated responses from 40 closed tickets.
CSAT per agent and per channel, with a small-sample warning
Add a criterion for the agent to both counts. The bottom uses ">=1" instead of COUNT, because it counts numeric ratings for that agent only. Priya is at 100% from 4 ratings, Marcus 67% from 3 and Jo 33% from 3. The same pattern by channel gives Chat 100%, Email 75% and Phone 33%.
Those per-agent numbers are almost meaningless: one more bad rating moves Marcus from 67% to 50%. Put a minimum number of responses in a cell (F5) and show a warning instead of a percentage below it. Many teams use 20 to 30 responses per period before reporting an individual's CSAT; the sample uses 4 so you can see both outcomes.
For every agent at once in Excel 365, =UNIQUE(B2:B13) and =COUNTIFS(B2:B13,UNIQUE(B2:B13),D2:D13,">=1") beside it list each agent with their number of ratings (4, 3, 3).
Result: Too few (3)
Too few (3): Jo has only 3 ratings, below the minimum of 4 in F5, so no percentage is shown.
Common mistakes
Reporting the average rating as CSAT
CSAT is a percentage: 4s and 5s divided by all ratings. A 3.8 average and 70% CSAT come from the same data.
Dividing by COUNTA or all rows
Use COUNT (or COUNTIFS with ">=1") so N/A answers and blanks stay out of the bottom of the fraction.
Comparisons that count text as satisfied
"N/A">=4 is TRUE in Excel. Use COUNTIFS, or add ISNUMBER to any SUMPRODUCT test.
Ranking agents on a handful of responses
Show a "Too few" warning below a minimum count and report CSAT per agent over a longer period.
FAQ
What is the formula for CSAT in Excel?
=COUNTIFS(D2:D13,">=4")/COUNT(D2:D13), formatted as a percentage. It's the share of numeric ratings that are 4 or 5 on a 1-5 scale. For CSAT next to FCR, AHT and service level, see Excel for call centers.
What is the difference between CSAT and NPS?
CSAT asks about satisfaction with one interaction, usually on 1-5. NPS asks how likely someone is to recommend you on 0-10, and subtracts detractors from promoters. See how to calculate NPS in Excel.
Do neutral 3 ratings count in CSAT?
They count in the total responses but not as satisfied. That's why moving people from 3 to 4 raises CSAT while barely changing the average.
How do I calculate CSAT by month in Excel?
Add date criteria to both counts, for example =COUNTIFS(D:D,">=4",E:E,">="&DATE(2026,11,1),E:E,"<"&DATE(2026,12,1)) divided by the same COUNTIFS with ">=1" instead of ">=4".
Functions used
COUNTIFSCounts with several conditionsCOUNTCounts numbersCOUNTACounts non-empty cellsAVERAGEMean of numbersIFPicks a result from a testUNIQUERemoves duplicates, spillsFILTERKeeps matching rows, spillsISNUMBERIs it a number?SUMPRODUCTMultiply then add
Related guides
- How to calculate NPS in Excel
- How to analyze Likert scale data in Excel
- How to calculate first contact resolution (FCR) in Excel
- How to calculate average handle time (AHT) in Excel
- How to calculate customer effort score (CES) in Excel
- Excel formulas cheat sheet: the 25 everyone should know
- Excel chart not updating: 7 fixes
Updated