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
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

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 :)
 

Forum statistics

Threads
1,141,924
Messages
5,709,375
Members
421,633
Latest member
Ubergribbler

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top