COUNTIF

[Mike_]

I run a weekly competition and I record the results as below

	          Week1	Week2	Week3	Week4

Alan	        50	  100		        50
Brian		                    30	
Charlie	     30      50	   12	   90
David	  	  18	
Ed	                  20	   40		30
George

I would like to add a column counting how many times each participant wins the comp. How do I do this?
ie

Alan       2
Brian      0
Charlie    1
David      0
Ed         1
George     0

 

[njimack]

Book3
	A	B	C	D	E	F
1		Week1	Week2	Week3	Week4	Wins
2		50	100	40	90
3	Alan	50	100		50	2
4	Brian			30		0
5	Charlie	30	50	12	90	1
6	David	18				0
7	Ed		20	40	30	1
8	George					0
Sheet1

Formulae to use:
B2:=MAX(B3:B8) (copy this across columns C:E)
F3:==IF(B3=B$2,1,0)+(IF(C3=C$2,1,0)+IF(D3=D$2,1,0)+IF(E3=E$2,1,0)) (copy this down rows 4:8)

 

[Mike_]

Thanks Neil. I reached this kind of code myself.

However, in my actual database, my competition has been running for about 100 weeks, won't my code be too long if I do this way ?


Mike

 

[erik.van.geit]

Hi,

assuming there will be more weeks to come, I would put the formula at the left and references till the end of row, using SUMPRODUCT

  A         B  C     D     E     F     
1              Week1 Week2 Week3 Week4 
2              50    100   40    90    
3 Alan      2  50    100         50    
4 Brian     0              30          
5 Charlie   1  30    50    12    90    
6 David     0  18                      
7 Ed        1        20    40    30    
8 George    0                          

test
[Table-It] version 06 by Erik Van Geit

RANGE FORMULA (1st cell)
B3:B8 =SUMPRODUCT(--(C3:IV3=$C$2:$IV$2),--($C$2:$IV$2<>""))
C2:F2 =MAX(C3:C8)

[Table-It] version 06 by Erik Van Geit

perhaps a guru will come up with a formula without helper row (second row, which you can hide)

kind regards,
Erik

 

[Mike_]

Fantastic. Many thanks.

 

[erik.van.geit]

you're WELCOME !!
it's a happy fact you recieved a solution at the same time you asked the question