Excel COUNTIFS for CSAT ratings including zero but w/o blank cells

Question

I have a set of data from customer survey ratings, 0-10, as well as data where no rating is given which reflects as blank. Column A has the agent name and column G has the rating.

On another spreadsheet, I have a little grid with agent names in the rows and then "positive 9-10", "neutral 6-8", and "poor 0-5" in the columns. None of the formulas I have tried are working correctly to count only within the range for each of the 3 criteria, to count the number of zeros as well, but NOT to count the blank cells. This also needs to reference the name in column A. So essentially Joe Smith has 3 positives, 0 neutral, and 1 poor, even though Joe had 10 surveys completed but only 4 of them had numerical grades posted.

I've tried the following, with N49 being the agent name as found in column A and then G is the ratings...

=COUNTIFS('Surveys'!A$2:A$2001,N49,'Surveys'!G$2:G$2001,">=0",'Surveys'!G$2:G$2001,">=5",'Surveys'!G$2:G$2001,"<>")

=COUNTIFS('Surveys'!A$2:A$2001,N49,'Surveys'!G$2:G$2001,">=0",'Surveys'!G$2:G$2001,">=5",'Surveys'!G$2:G$2001,”<>”&””)

=COUNTIFS('Surveys'!A$2:A$2001,N49,'Surveys'!G$2:G$2001,">=0" "&<=5",'Surveys'!G$2:G$2001,"<>")

The first formula seems to be the closest to what I need but it's counting any grade, not just within the 0-5 range.

I didn't have a problem with the formula for the positives as

=COUNTIFS('Surveys'!A$2:A$2001,N49,'Surveys'!G$2:G$2001,">=9") 

But I'm having the same problem with the Neutrals 6-8 as well, when using this formula:

=COUNTIFS('Surveys'!A$2:A$2001,N49,'Surveys'!G$2:G$2001,">5",'Surveys'!G$2:G$2001,">9")

Any help is appreciated, thank you!

Answer 1

Initial Notes

• COUNTIFS will not equate 0 with NULL
• You have mixed in invalid quotation marks ”<>”&””). Valid " Invalid ” maybe copying code from another source.

Formulas w/ Named Ranges

Where ranges are named:
Agent:   A2:Ax
Rating: B2:Bx

           H
  |---------------------------------------------------|
1 | =COUNTIFS(Rating,">=9",Rating,"<=10",Agent,"Bob") |
  |---------------------------------------------------|
2 | =COUNTIFS(Rating,">=6",Rating,"<=8",Agent,"Bob")  |
  |---------------------------------------------------|
3 | =COUNTIFS(Rating,">=0",Rating,"<=5",Agent,"Bob")  |
  |---------------------------------------------------|

Formulas w/out Named Ranges

           H
  |----------------------------------------------------------------|
1 | Bob                                                            | 
  |----------------------------------------------------------------| 
2 | =COUNTIFS($B$2:$B$9,">="&$D2,$B$2:$B$9,"<="&$E2,$A$2:$A$9,H$1) | 
  |----------------------------------------------------------------| 
3 | =COUNTIFS($B$2:$B$9,">="&$D3,$B$2:$B$9,"<="&$E3,$A$2:$A$9,H$1) | 
  |----------------------------------------------------------------| 
4 | =COUNTIFS($B$2:$B$9,">="&$D4,$B$2:$B$9,"<="&$E4,$A$2:$A$9,H$1) | 
  |----------------------------------------------------------------| 

Sample Data

      A        B
  |-------|--------|
1 | Agent | Rating |
  |-------|--------|
2 | Bob   | 4      |
  |-------|--------|
3 | Bob   | 5      |
  |-------|--------|
4 | Bob   | 10     |
  |-------|--------|
5 | John  | 6      |
  |-------|--------|
6 | Bob   | 10     |
  |-------|--------|
7 | John  | 5      |
  |-------|--------|
8 | Bob   | 3      |
  |-------|--------|
9 | John  | 4      |
  |-------|--------|

Calculations

     D     E    F      G         H     I
  |-----|-----|---|-----------|-----|------|
1 | Min | Max |   |           | Bob | John |
  |-----|-----|---|-----------|-----|------|
2 | 9   | 10  |   | Positive: | 2   | 0    |
  |-----|-----|---|-----------|-----|------|
3 | 6   | 8   |   | Neutral:  | 0   | 1    |
  |-----|-----|---|-----------|-----|------|
5 | 0   | 5   |   | Poor:     | 3   | 2    |
  |-----|-----|---|-----------|-----|------|