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!
>
and<
signs, also tryNOT(ISBLANK())