COUNTIF

Mike_

New Member
Joined
Oct 12, 2006
Messages
3
I run a weekly competition and I record the results as below

Code:
	          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
Code:
Alan       2
Brian      0
Charlie    1
David      0
Ed         1
George     0
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,764
Book3
ABCDEF
1Week1Week2Week3Week4Wins
2501004090
3Alan50100502
4Brian300
5Charlie305012901
6David180
7Ed2040301
8George0
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_

New Member
Joined
Oct 12, 2006
Messages
3
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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
Code:
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
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
you're WELCOME !!
it's a happy fact you recieved a solution at the same time you asked the question :)
 

Watch MrExcel Video

Forum statistics

Threads
1,114,053
Messages
5,545,738
Members
410,703
Latest member
yaronjoseph
Top