Countif Question

P

pfeff

I need a count of features that have equal values and separated by wh
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 anothe
person?

Thanks in advance!

Da
 
J

JE McGimpsey

Assuming these are in 5 separate columns:

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

pfeff

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

Dan
 
P

pfeff

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
 
S

Sandy Mann

pfeff,

If I understand you correctly then:

=SUMPRODUCT(((A1:A6="Dan")+(C1:C6="Dan"))*(B1:B6=D1: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:B6>D1:D6))

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

=SUMPRODUCT(((A1:A6="Dan")*(B1:B6>D1:D6))+((C1:C6="Dan")*(D1:D6>B1: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
[email protected]
[email protected] with @tiscali.co.uk
 
P

pfeff

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