How to calculate NPS in Excel

The NPS formula in one cell

The sheet holds 13 replies to "How likely are you to recommend us?" from a support and a sales team. One person skipped the question, one typed N/A, and one 9 was pasted in as text. That is what real survey exports look like.

COUNTIF(C2:C14,">=9") counts the promoters (5) and COUNTIF(C2:C14,"<=6") the detractors (3). Passives, the 7s and 8s, are not in the formula, but they still count in the total. COUNT(C2:C14) gives 10, the number of real scores. 5 minus 3 is 2, divided by 10 is 0.2, times 100 is an NPS of 20.

A promoters, passives and detractors table

Reports usually show the three groups next to the score, because an NPS of 20 can come from very different mixes. Put each share in its own cell and format them as percentages (Home > Number > %).

Promoters: =COUNTIF(C2:C14,">=9")/COUNT(C2:C14) gives 50%. Passives: =COUNTIFS(C2:C14,">=7",C2:C14,"<=8")/COUNT(C2:C14) gives 20%. Detractors: =COUNTIF(C2:C14,"<=6")/COUNT(C2:C14) gives 30%. The three add up to 100%, which is a quick check that every score fell into a group.

NPS is then promoters minus detractors. Leave it as a whole number: write 20, not 20%. NPS is a score on a -100 to +100 scale, not a percentage of anything, and showing it with a % sign is a common reporting mistake.

NPS by team with COUNTIFS

To split the score by team, add the team as a condition to every count. E2 holds Support and E3 holds Sales, so you can write the formula once next to E2 and fill it down.

The divisor needs care. COUNTIFS has no "is a number" test, but the condition ">=0" only matches numbers, so COUNTIFS(B2:B14,E2,C2:C14,">=0") counts the team's real scores and skips blanks and text. Do not use "<>" for this: it counts every non-empty cell, so Support gets 6 replies instead of 4.

Support scores 25 and Sales scores 17 (16.67 before rounding). With only 4 and 6 answers, single replies swing the score: one extra promoter would lift Support from 25 to 40. Show the number of responses beside each score.

NPS by month, and ROUND vs ROUNDUP

For a monthly trend, put the first day of each month in G2 and G3 and count dates from that day up to the start of the next month: ">="&G2 and "<"&EDATE(G2,1). This works for any month length and needs no helper column. The response dates must be real dates, not text.

September comes to 33.33. Round to a whole number with ROUND, which gives 33. ROUNDUP always rounds away from zero, so it turns 33.33 into 34 and makes positive months look better than they are. On negative scores it goes the other way: ROUNDUP(-16.2,0) is -17, where ROUND gives -16. Most survey tools show NPS as a whole number, so round it the same way when you compare with their dashboards.

October scores 0 as the data stands: 2 promoters and 2 detractors out of 4 numbers. The text 9 from October 13 is missing from that month, which is the next problem.

Scores typed as text, blanks and N/A

Survey exports and copy-pastes often bring numbers in as text. COUNTIF with ">=9" or "<=6" only compares real numbers, and COUNT skips text, so the 9 in C10 is silently dropped from everything. Compare COUNTA with COUNT to find out: a gap means text is hiding in the column. Here the gap is 2, the N/A and the text 9.

Watch for formulas that treat text differently. =COUNTIF(C2:C14,9) returns 3, because an exact match does count the text "9". And =SUMPRODUCT(--(C2:C14>=9)) returns 7, because in a direct comparison Excel ranks any text above every number, so N/A counts as a promoter. Fixing the data is safer than working around it.

Once C10 holds a real 9, the overall NPS goes from 20 to 27 (27.27 before rounding), Support from 25 to 40, and October from 0 to 20. A blank or N/A should stay out of the count: someone who did not answer is not a detractor. That is why COUNT, not COUNTA, is the divisor. Dividing by COUNTA here gives 16.67.

1. Select the score column (C2:C14).
2. If cells show a green triangle, click the warning icon next to the selection and choose Convert to Number.
3. Otherwise use Data > Text to Columns and click Finish straight away. Text that looks like a number becomes a number; N/A stays text and stays out of the count.
4. To stop new typos, use Data > Data Validation, allow Whole number between 0 and 10.