Count of Value based on another value

[Richhall]

HI

I want to do a count of a value if another value is displayed in
another column. Its basically if someone gets something correct, I
want to know how many times they have got a particular answer. So a 1
in a column means that person has got the answer right, then if they
have a 1 i want to count how many times they have got a certain answer
against that 1?

I.e THis is a very long list, with a number of people and I want to
get stats.

Column A Column B COlumn C Column D
Answer Person A Person B Person C

Milk 1
1 0
Coke 1 1
1
Orange 0 1
1
Orange 1 1
0
Coke 0 1
0
Milk 0
1 1
Milk 1
1 1
Milk 0
1 0

Therefore

Person A has 4 correct
Person B has them all correct (8)
Person C has 4 correct

I want to create a summary though:


Person A Person B
Person C
Milk 2
4 2
Coke 1
2 1
Orange 1
2 1
TOTAL 4
8 4

Can anyone help please?

Cheers

Rich

 

[bob.golightly]

HI

I want to do a count of a value if another value is displayed in
another column. Its basically if someone gets something correct, I
want to know how many times they have got a particular answer. So a 1
in a column means that person has got the answer right, then if they
have a 1 i want to count how many times they have got a certain answer
against that 1?

I.e THis is a very long list, with a number of people and I want to
get stats.

Column A Column B COlumn C Column D
Answer Person A Person B Person C

Milk 1
1 0
Coke 1 1
1
Orange 0 1
1
Orange 1 1
0
Coke 0 1
0
Milk 0
1 1
Milk 1
1 1
Milk 0
1 0

Therefore

Person A has 4 correct
Person B has them all correct (8)
Person C has 4 correct

I want to create a summary though:

Person A Person B
Person C
Milk 2
4 2
Coke 1
2 1
Orange 1
2 1
TOTAL 4
8 4

Can anyone help please?

Cheers

Rich

Person A Person
B Person C
Milk 1
1 0
Coke 1
1 1
Orange0
1 1
Orange1
1 0
Coke 0
1 0
Milk 0
1 1
Milk 1
1 1
Milk 0
1 0
=COUNTIF(B2:B9,1)
=COUNTIF(C2:C9,1) =COUNTIF(D29,1)

Person A Person
B Person C
Milk =SUMPRODUCT(($A$2:$A$9=$A$13)*(B$2:B$9=1)) =SUMPRODUCT(($A$2:$A
$9=$A$13)*(C$2:C$9=1)) =SUMPRODUCT(($A$2:$A$9=$A$13)*(D$2$9=1))
Coke =SUMPRODUCT(($A$2:$A$9=$A$14)*(B$2:B$9=1)) =SUMPRODUCT(($A$2:$A
$9=$A$14)*(C$2:C$9=1)) =SUMPRODUCT(($A$2:$A$9=$A$14)*(D$2$9=1))
Orange=SUMPRODUCT(($A$2:$A$9=$A$15)*(B$2:B$9=1)) =SUMPRODUCT(($A$2:$A
$9=$A$15)*(C$2:C$9=1)) =SUMPRODUCT(($A$2:$A$9=$A$15)*(D$2$9=1))
TOTAL =SUM(B13:B15)
=SUM(C13:C15) =SUM(D1315)

 

[bob.golightly]

HI

I want to do a count of a value if another value is displayed in
another column. Its basically if someone gets something correct, I
want to know how many times they have got a particular answer. So a 1
in a column means that person has got the answer right, then if they
have a 1 i want to count how many times they have got a certain answer
against that 1?

I.e THis is a very long list, with a number of people and I want to
get stats.

Column A Column B COlumn C Column D
Answer Person A Person B Person C

Milk 1
1 0
Coke 1 1
1
Orange 0 1
1
Orange 1 1
0
Coke 0 1
0
Milk 0
1 1
Milk 1
1 1
Milk 0
1 0

Therefore

Person A has 4 correct
Person B has them all correct (8)
Person C has 4 correct

I want to create a summary though:

Person A Person B
Person C
Milk 2
4 2
Coke 1
2 1
Orange 1
2 1
TOTAL 4
8 4

Can anyone help please?

Cheers

Rich

Use =COUNTIF to add person A B C columns
e.g. =COUNTIF(B2:B9,1)
where B2:B9 contains the 1s and 0s for Person A
repeat for each column

To total where a 1 is recorded against a specific text description
e.g. for Person A
=SUMPRODUCT(($A$2:$A$9=$A$13)*(B$2:B$9=1))
where $A2:$A9 refers to the range containing the text descriptions and
$A$13 refers to a cell containing the word to be counted e.g. "milk"
and $B$2:$B$9 contains the 1s and 0s for Person A

Hope this helps,
(e-mail address removed)