Cellwise Start free

Excel guide · 4 min read

How to calculate CSAT score in Excel

Quick answerCSAT is the percentage of respondents who were satisfied, usually those who rated 4 or 5 on a 1-5 scale. With ratings in D2:D13, use =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.

RowABCDEF
1TicketAgentChannelRating (1-5)Tickets closed
2T-1041PriyaChat540
3T-1042MarcusEmail4
4T-1043PriyaPhone5Min responses
5T-1044JoEmail34
6T-1045MarcusChatN/A
7T-1046PriyaChat5
8T-1047JoPhone2
9T-1048MarcusEmail4
10T-1049JoChat
11T-1050PriyaEmail4
12T-1051MarcusPhone1
13T-1052JoChat5

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.

=COUNTIFS(D2:D13,">=4")/COUNT(D2:D13)

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.

=AVERAGE(D2:D13)

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.

=COUNTIFS(D2:D13,">=4")/COUNTA(D2:D13)

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.

=COUNT(D2:D13)/F2

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).

=IF(COUNTIFS(B2:B13,"Jo",D2:D13,">=1")<F5,"Too few ("&COUNTIFS(B2:B13,"Jo",D2:D13,">=1")&")",COUNTIFS(B2:B13,"Jo",D2:D13,">=4")/COUNTIFS(B2:B13,"Jo",D2:D13,">=1"))

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

Related guides

Updated