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

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
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)
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

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
Back
Top