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.
--
VCE teacher

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)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"denizcan" wrote:
it is a very 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.
--
VCE teacher

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?
--
VCE teacher


"Max" wrote:

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)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"denizcan" wrote:
it is a very 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.
--
VCE teacher

Glad you adapted it to suit.
Thanks for posting back
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"denizcan" wrote in message
...
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?
--
VCE teacher