In this post we will make ourselves a simple but effective Net Promoter Score calculator in Excel and Google Sheets using the COUNTIF function.

Before we do that let’s have a quick review of the formula for NPS.

**RELATED CONTENT**

More Net Promoter Score data analysis tools

## Calculating Net Promoter Score in Excel and Google Sheets

NPS is calculated by using the responses to the following customer survey question:

You will notice the response is an integer (whole number) between 0 and 10 – in the standard questions no fractional responses are allowed, e.g. 9.5.

We then define three groups of respondents:

**Promoters**: are responses of 9 or 10**Neutrals**: are responses of 7 or 8; and**Detractors**are responses of 0 to 6.

Then the formula for NPS is:

It will make our spreadsheet easier to create if we apply a little algebra and re-write the equation as:

This score, although it looks like a percentage, should be referred to as a number.

You can see that NPS ranges all the way from:

- -100: where you have 100% Detractors to
- +100: where you have 100% Promoters

## Building an NPS Calculator in Excel or Google Sheets

Now we have the basic equation we can calculate the score in a couple of different ways in Excel and Google Sheets.

The easiest way to calculate it is to use the Excel COUNTIF function or the, basically identical, Google Sheets COUNTIF version:.

This function allows you to count the number of times the cell contents meets a certain criteria.

In this case we’ll count the responses in each of the three categories we are interested in: Detractors, Neutrals and Promoters.

For Promoters we’re looking for 9’s or 10’s and so the function looks like this: **=COUNTIF(R:R,”>=9″)**

For Detractors we’re looking for 0’s up to 6’s so the formula is : **=COUNTIF(R:R,”<=6″)**

And lastly for Neutrals we’re looking for just 7’s and 8’s: **=COUNTIF(R:R,”=7″) +COUNTIF(R:R,”=8″)**

In this case ** R:R** is the whole column of responses that you have received for your survey.

The nice thing about using this function is that you can just copy and paste your scores into the column and Excel will count them for you.

Now that you have the number of Promoters, Neutrals and Detractors you can add the equation for NPS to the spreadsheet.

This is how the calculation looks in the spreadsheet itself. Remember to include the “(” and “)” or the calculation will be wrong.

Of course, you don’t have to do these two calculations separately. You can combine them into the one formula. Here, again, **R:R** is the column where you have your scores.