| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| formula for counting the occurence of more than one condition | Excel Worksheet Functions | |||
| Multiple Criteria pulled from Drop Down Lists | Excel Discussion (Misc queries) | |||
| counting with multiple criteria | Excel Worksheet Functions | |||
| Counting Cells with multiple criteria.One criteria supporting wild | Excel Worksheet Functions | |||
| Counting Cells with multiple criteria.One criteria supporting wild | Excel Worksheet Functions | |||
Linear Mode
