Calculate NPS - help with formulas

[devett]

I'm having some challenges calculating NPS in a spreadsheet.

I have the total NPS score, using formula =100*(COUNTIF(K:K;">8")-COUNTIF(K:K;"<5"))/COUNT(K:K) - This calculate the total NPS of all rows i column K in my spreadsheet.

My challenge is, that I need to calculate the same number, but only for the rows with the value "Backoffice" in my column F. How do I do this?

 

[BrianMH]

Use countifs if you are on office 2007 or above.

otherwise

=100*((SUMPRODUCT(--(K:K>8),--(F:F="Backoffice")))-SUMPRODUCT(--(K:K<5),--(F:F="Backoffice"))/COUNTIF(F:F,"Backoffice"))

 

[devett]

Use countifs if you are on office 2007 or above.

otherwise

=100*((SUMPRODUCT(--(K:K>8),--(F:F="Backoffice")))-SUMPRODUCT(--(K:K<5),--(F:F="Backoffice"))/COUNTIF(F:F,"Backoffice"))

It's not working for me at all. I'm using Excel 2007. This returns an error:

=100*((COUNTIFS(--(K:K>8);--(F:F="Backoffice")))-COUNTIFS(--(K:K<5);(F:F="Backoffice"))/COUNTIF(F:F;"Backoffice"))

I've changes your commas, since my excel seems to need semicolon instead, but still not working? I tried this too, it returns a number, but that seems totally off:

=100*((SUMPRODUCT(--(K:K>8);--(F:F="Backoffice")))-SUMPRODUCT(--(K:K<5);--(F:F="Backoffice"))/COUNTIF(F:F;"Backoffice"))

This returns the number 364,3 which seems way to high? The correct value should be -7,1 when I have this data:

[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Backoffice[/TD]
[TD="width: 64, align: right"]0[/TD]
[/TR]
[TR]
[TD]Backoffice[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]Backoffice[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Backoffice[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]Backoffice[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]Backoffice[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Backoffice[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Backoffice[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Backoffice[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Backoffice[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]Backoffice[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Backoffice[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Backoffice[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]Backoffice[/TD]
[TD="align: right"]8[/TD]
[/TR]
</tbody>[/TABLE]

 

[devett]

Use countifs if you are on office 2007 or above.

otherwise

=100*((SUMPRODUCT(--(K:K>8),--(F:F="Backoffice")))-SUMPRODUCT(--(K:K<5),--(F:F="Backoffice"))/COUNTIF(F:F,"Backoffice"))

I've figured it out, this is working now (don't mind that the columns has changed)

=100*(((SUMPRODUCT(--(H:H>8);--(G:G="Backoffice")))-SUMPRODUCT(--(H:H<5);--(G:G="Backoffice")))/COUNTIF(G:G;"Backoffice"))