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

 

[Gopal Pradhan]

The format in both the sheets are the same. Have used the Trim formula too. Data matched for April and have discrepancies for May.
In Raw Dump there are 4 rows for May and for June there are approx 20 rows with 20 responses. Attached is the screenshot for the NPS and the RawDump

 

[jasonb75]

Because you haven't edited the formula correctly. As I said before, there were a lot of assumptions in the formula to make up for missing information, which was why I added this to the reply so that you could edit the formula correctly.

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))

Last guess based on the information provided. Again this must go into B2 then you drag it to fill the sheet as necessary.

=SUMPRODUCT(('RawDump'!$A$2:$A$22=$A2)*('RawDump'!$B$2:$G$22=B$1))

As I can't see the end of your data you will have to change the parts in red to match the last row and last column used.

The row number must be the same in both parts.

Do not change anything that is not red.

 

[Gopal Pradhan]

Thank you for the response.. I am getting the values but they seem to be incorrect...
is there any way that i can share the excel to you and you can have a look ?

 

[jasonb75]

One thing to take into consideration, empty cells will be counted as having a value of 0

You can either use the XL2BB add in to post a full capture to the forum (see link below) or you can upload your file to a sharing service such as dropbox and post a link here.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

I'll have a look at it as soon as I get chance, but most likely that will not be until tomorrow.

 

[Gopal Pradhan]

Thank You for all your help.. it's working now