# COUNTIF

#### Mike_

##### New Member
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

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

#### njimack

##### Well-known Member
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
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
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

#### Mike_

##### New Member
Fantastic. Many thanks.

#### erik.van.geit

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

Replies
3
Views
117
Replies
2
Views
205
Replies
3
Views
168
Replies
5
Views
220
Replies
9
Views
274

1,191,168
Messages
5,985,054
Members
439,936
Latest member
BSR

### 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.

### Which adblocker are you using?

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

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