Lookup and Match

[Gopal Pradhan]

I am performing a NPS survey and with in that given data i have my month and my survey results horizontally. Below is an example

0 1 2 3 4 5 6 7 8 9 10
Jan
Feb
March
April

So i am here calculating the NPS calculation month wise. So end result am looking at is, for January month, of the responses how many 0, 1 ,2 3 (looking for count).

The data in my other sheet i am trying to lookup and match is something like below : ( There are total 6 questions out of which the responses are received )

Q1 Q2 Q3 Q4 Q5 A6
January 0 4 10 9 3 10
February 2 5 8 9 2 7
February 0 4 10 9 4 10
February 0 4 10 9 6 10
March 0 4 10 9 9 10
March 0 4 10 9 10 10

So my end dashboard result would be, in a given month how many 0 to 10 i have received separately

 

[jasonb75]

You can do that with sumproduct, enter this formula into B2 of the dashboard then drag right and down.

=SUMPRODUCT((Dump!$A$2:$A$10=$A2)*(Dump!$B$2:$G$10=B$1))

 

[Gopal Pradhan]

Thank you for the formula, but that will give me the count of month. But the value am trying to find out is for a given month, the end result would be how many 0, 1 ,2 3, 4 have received from the responses.
In a given month i might receive one response or 10. The responses are dependant on the 6 questions in first screenshot of dump, where A column will have month and C to H are the 6 questionnaire where business partners reply or rate from 1 to 10

 

[jasonb75]

The formula will give you what you have asked for.

For April and 0 it will give a count of 1, for April and 10 it will give a count of 5.

 

[Gopal Pradhan]

Sorry, but not getting the output. Please find the attached image

 

[Gopal Pradhan]

=SUMPRODUCT((NPS Scoring Trend Month Wise !$A$2:$A$10=$A2)*(RawDump!$B$2:$G$10=B$1))

The tab for Dashboard is : NPS Scoring Trend Month Wise
RawDump
The questionnaire in dump would start from C2 to H2

 

[jasonb75]

My answer is only as good as your question, your original post had no sheet names or data ranges so I had to make assumptions based on the information that you did provide.

Is there a space after 'Wise' in your sheet name? If there is not then you need to delete the space in the formula, it must match exactly.

Note that I have marked 2 ranges in bold, the first one must point to the month names in the 'Dump' image, the second one must point to the scores in the 'Dump' image.

=SUMPRODUCT(('NPS Scoring Trend Month Wise '!$A$2:$A$10=$A2)*('NPS Scoring Trend Month Wise '!$B$2:$G$10=B$1))

 

[Gopal Pradhan]

Here is a clear screenshot of the headers...apologies and thanks a ton for your help in advance

 

[Gopal Pradhan]

Thanks again..got the correct data for April, but for May its not the desired outcome.. Attached are the screenshots

 

[jasonb75]

Did you edit the formula to match the sheet?

If done correctly it will work fine. I can't see from the screen capture if there are anomalies in the data.

"May" and "May " are not the same to the formula, likewise if the month names are not text but are actually proper dates formatted as month names then a different day would not be seen.