Counting Occurence of Criteria in Multiple Lists of Data
 

I was wondering if there was a way to count the amount of times that
something occurs based on two sets of data. Take for example a list of
people's favorite fruits and sports:

Fruit Sports
Apple Hockey
Orange Soccer
Banana Football
Apple Hockey
Apple Soccer
Orange Hockey

How could I count the number of times that a person both liked Apples and
Hockey? Please let me know if you need a further explanation of what I am
looking for. Thanks in advance.

Counting Occurence of Criteria in Multiple Lists of Data
 

In C1 enter
=SUMPRODUCT(($A$2:$A$7="Apple")*($B$2:$B$7="Hockey "))


"hockey14" wrote:

I was wondering if there was a way to count the amount of times that
something occurs based on two sets of data. Take for example a list of
people's favorite fruits and sports:

Fruit Sports
Apple Hockey
Orange Soccer
Banana Football
Apple Hockey
Apple Soccer
Orange Hockey

How could I count the number of times that a person both liked Apples and
Hockey? Please let me know if you need a further explanation of what I am
looking for. Thanks in advance.

Counting Occurence of Criteria in Multiple Lists of Data
 

Unfortunately, when I use the SUMPRODUCT formula, it gives me a value of 0 in
that column. Is there something that I am doing wrong??

"Jim May" wrote:

In C1 enter
=SUMPRODUCT(($A$2:$A$7="Apple")*($B$2:$B$7="Hockey "))


"hockey14" wrote:

I was wondering if there was a way to count the amount of times that
something occurs based on two sets of data. Take for example a list of
people's favorite fruits and sports:

Fruit Sports
Apple Hockey
Orange Soccer
Banana Football
Apple Hockey
Apple Soccer
Orange Hockey

How could I count the number of times that a person both liked Apples and
Hockey? Please let me know if you need a further explanation of what I am
looking for. Thanks in advance.

Counting Occurence of Criteria in Multiple Lists of Data
 

Move to the cell with the formula, press F2 to go to EDIT mode and the press
<Ctrl + <Shift + <Enter to enter the formula as an array type formula.
--
Kevin Backmann


"hockey14" wrote:

Unfortunately, when I use the SUMPRODUCT formula, it gives me a value of 0 in
that column. Is there something that I am doing wrong??

"Jim May" wrote:

In C1 enter
=SUMPRODUCT(($A$2:$A$7="Apple")*($B$2:$B$7="Hockey "))


"hockey14" wrote:

I was wondering if there was a way to count the amount of times that
something occurs based on two sets of data. Take for example a list of
people's favorite fruits and sports:

Fruit Sports
Apple Hockey
Orange Soccer
Banana Football
Apple Hockey
Apple Soccer
Orange Hockey

How could I count the number of times that a person both liked Apples and
Hockey? Please let me know if you need a further explanation of what I am
looking for. Thanks in advance.

Counting Occurence of Criteria in Multiple Lists of Data
 

Not necessary to array enter unless the OP used some other formula (such as
transpose or frequency) within the sumproduct formula that requires it.

Perhaps he should double check the data to make sure it actually matches (ie
- no trailing spaces).


"Kevin B" wrote:

Move to the cell with the formula, press F2 to go to EDIT mode and the press
<Ctrl + <Shift + <Enter to enter the formula as an array type formula.
--
Kevin Backmann


"hockey14" wrote:

Unfortunately, when I use the SUMPRODUCT formula, it gives me a value of 0 in
that column. Is there something that I am doing wrong??

"Jim May" wrote:

In C1 enter
=SUMPRODUCT(($A$2:$A$7="Apple")*($B$2:$B$7="Hockey "))


"hockey14" wrote:

I was wondering if there was a way to count the amount of times that
something occurs based on two sets of data. Take for example a list of
people's favorite fruits and sports:

Fruit Sports
Apple Hockey
Orange Soccer
Banana Football
Apple Hockey
Apple Soccer
Orange Hockey

How could I count the number of times that a person both liked Apples and
Hockey? Please let me know if you need a further explanation of what I am
looking for. Thanks in advance.