matching two values

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

it is avery simple qeustion for most of you I know. But I could not figure it
out. I have got VCE students who prefered 6 subjects from 15 subjects on the
preference list. And all I want to know is intersection of these subjects.
i.e. how many times say, Physics have been preferred with Economics
concurrently.
 
Suppose the 6 subject preferences are listed in cols B to G, from row2 down

Put in H2:
=SUMPRODUCT(COUNTIF(B2:G2,{"Physics","Economics"}))=2
Copy down all the way. Then you could apply autofilter on col H, filter for
TRUE

And if you need the count of cases,
Put in I1: =COUNTIF(H:H,TRUE)
 
Thanks Max. I tried your suggestion, however it did not work but gave me a
different idea. and I found a very simple answer! Thanks again..

=sumproduct(a2:a40,b2:b40)

as I put 1 for preference and 0 for not choosing that subject, if both
subjects were chosen it gave me product of 1 otherwise 0. was not it simple
and sweet?
 
Back
Top