Sorting and summation

[jb]

Hi experts,

I have one excel sheet containing feedback of 400 customers who have filled a questionnaire of 50 objective questions. Each question has 5 columns against the question. Customer will tick mark in one of the column.

I have entered data in excel sheet.
Cell B1, C1, and so on contains title Q-1, Q-2 and so on.
Cell A2, A3 and so on contains title Form-1, Form-2 and so on.

Second row contains feedback of first customer, third row contains feedback of second customer and so on.

If customer has ticked in 1st column of any question then data entered in excel is 1, 2nd column = 2 and so on.

Last cell of each row contains sum of all answer value given by customer.
I need your help for following problem.

I want to calculate total number of customers who have given answer 1 or 2. But only first 50 highest scorer should be considered for this calculation.

This process is to be done for all questions in a new sheet which is having following format.

      Customers with answer 1 or 2
Q-1    30 
Q-2    10
Q-3    5
:
:
Q-50

Is it possible that without performing sorting on original data entry, we can calculate how many from highest 50 scorer's have given answer 1 or 2.

 

[Luke M]

I'm not sure of your exact layout; I got a little confused. But I think the formula would look something like this:
=SUMPRODUCT(1*((TotalRange>=LARGE(TotalRange,50))*(Qcolumn)=1))

TotalRange is the range where you have the total score for each person. Qcolumn is the column representing Q1 answers.

 

[jb]

Hi Luke M,
I am attaching my excel file with this for your reference. My file contains 2 sheets. Data and analysis.
Data sheet contains feedback of 315 customers. I do not want to perform sorting on score.
But I want to calculate total number of customers who have given answer 1 or 2 and total number of customers who have given answer 4 or 5.
But only top 50 scorer customers should be considered in calculation.
Means out of top 50 scorer customers who have given answer 1 or 2 and 4 or 5.
I think now my question is clear.
Please help.

 

[NARAYANK991]

Hi ,

Can you check your file ?

Narayan

 

[jb]

Hi Narayanji,
I have checked my file where you have applied some formula.
Sirji, I am not good at excel formula so I am trying to understand the formula.
But sirji, there is one problem in output.
If we take top 50 scorers who have given answer 1 or 2 and
if we take top 50 scorers who have given answer 4 or 5 then
total of both column should not exceed 50 right?
Same is for bottom 50 scorers.
I think there is some correction required in formula.
Also I want know that how to define top_two, Qcolumns and totalrange words used in formula.
Please help.

 

[Luke M]

Hi jb.
Please see attached.
Sometime you have a total greater than 50 when there is a tie for the "50th scorer". E.g., if I want the top 3, but my numbers were 1,2,3,3 then technically I have 4 results, because there was a tie for 3rd.

 

[jb]

Thanks a lot Luke M.
Great help.
Thanks a lot.