Countif Question
 

I need a count of features that have equal values and separated by who
have the equal values.

test1 Dan 7 Brian 1
test2 Dan 3 Brian 3
test3 Fred 3 Dan 1
test4 Mike 3 Brian 3

i.e. how many times did each person have the same score as another
person?

Thanks in advance!

Dan


--
pfeff
------------------------------------------------------------------------
pfeff's Profile: http://www.excelforum.com/member.php...o&userid=29858
View this thread: http://www.excelforum.com/showthread...hreadid=495604

Countif Question
 

Assuming these are in 5 separate columns:

=SUMPRODUCT(--(C1:C100=E1:E100),--(C1:C100<""))


In article ,
pfeff wrote:

I need a count of features that have equal values and separated by who
have the equal values.

test1 Dan 7 Brian 1
test2 Dan 3 Brian 3
test3 Fred 3 Dan 1
test4 Mike 3 Brian 3

i.e. how many times did each person have the same score as another
person?

Thanks in advance!

Dan

Countif Question
 

Thanks JE! Does the same idea apply if I want to find out if score a is
higher than score b?

Dan


--
pfeff
------------------------------------------------------------------------
pfeff's Profile: http://www.excelforum.com/member.php...o&userid=29858
View this thread: http://www.excelforum.com/showthread...hreadid=495604

Countif Question
 

It didn't quite work they way I wanted it, I should probably explain
better. I need it to keep track of the scores by person. So I would
need to know how many times the scores matched. My array is below...

Col A Col B Col C Col D Col E
Test1 Dan 7 Brian 1
Test2 Dan 3 Brian 3
Test3 Brian 3 Dan 1
Test4 Dan 5 Brian 3
Test5 Mike 4 Phil 1
Test6 Ed 3 John 7


--
pfeff
------------------------------------------------------------------------
pfeff's Profile: http://www.excelforum.com/member.php...o&userid=29858
View this thread: http://www.excelforum.com/showthread...hreadid=495604

Countif Question
 

pfeff,

If I understand you correctly then:

=SUMPRODUCT(((A1:A6="Dan")+(C1:C6="Dan"))*(B1:B6=D 1:D6))

will return the count of the number of tests where Dan was in Column A or C
and it was a draw ( the + in this instance acts like an OR in an IF
statement and

=SUMPRODUCT((A1:A6="Dan")*(B1:B6D1:D6))

will give the number of times that Dan was in column A and won the match or

=SUMPRODUCT(((A1:A6="Dan")*(B1:B6D1:D6))+((C1:C6= "Dan")*(D1:D6B1:B6)))

gives the total number of tests that Dan won regardless of which column his
name appears in.

Note that the formulas will give wrong results if the operator adds say a
space or some other character in the cell but if that were to be an issue
then there are ways of dealing with it.

--
HTH

Sandy

with @tiscali.co.uk


"pfeff" wrote in
message ...

It didn't quite work they way I wanted it, I should probably explain
better. I need it to keep track of the scores by person. So I would
need to know how many times the scores matched. My array is below...

Col A Col B Col C Col D Col E
Test1 Dan 7 Brian 1
Test2 Dan 3 Brian 3
Test3 Brian 3 Dan 1
Test4 Dan 5 Brian 3
Test5 Mike 4 Phil 1
Test6 Ed 3 John 7


--
pfeff
------------------------------------------------------------------------
pfeff's Profile:
http://www.excelforum.com/member.php...o&userid=29858
View this thread: http://www.excelforum.com/showthread...hreadid=495604

Countif Question
 

I got it all to work. Many thanks and to all and have a safe and happy
holiday season!


--
pfeff
------------------------------------------------------------------------
pfeff's Profile: http://www.excelforum.com/member.php...o&userid=29858
View this thread: http://www.excelforum.com/showthread...hreadid=495604