count each row for total matches

  • Thread starter Thread starter Jace
  • Start date Start date
J

Jace

I have data from a survey( 49 questions) from 484 subjects. They had to
choose 7 questions. Each subject responses are entered in a row ( 484 rows)
as a number (from 1 to 49) each subject has 7 responses (7 cells) (7
columns). I want to select a group of questions at a time and see how many
subjects answered those questions. for example if i choose #3, 7,11,22,33 and
60 of my subjects match 3 or 4 of my numbers then at the end of the row i
want a total of how many matched ie 3 or 4.
 
One way

Assuming the reference set of 7 numbers is entered in A1:G1
and your 484 rows of source data starts in A2:G2 down
(the numbers in the reference set/source data need not be sorted)

Put this in say, H2:
=SUMPRODUCT(--ISNUMBER(MATCH(A2:G2,$A$1:$G$1,0)))
Copy H2 down to the last row of data. Col H will return the required # of
matched numbers within each row of source data.
 
Thanks for your help Max could you be a little more specific.
I want to know how many subjects responded to questions 3,11,27,33,46 i
have these #'s in each of my seven columns A1:G1 just the way i have typed
them here.
-is this correct?
-the formula that you suggested H2:(do i need to add H485)
=SUMPRODUCT(--ISNUMBER(MATCH(A2:G2,$A$1:$G$1,0)))
Where your(--ISNUMBER(MATCH(-WHAT DO I PUT THERE AND HOW SHOULD IT BE WRITTEN
-At the end of the formula you have 3))) is this a type O?
 
Back
Top