HELP with this formula

[albri98]

hi,

I have these tournament with my friends and made a formula but for some reason is not working i wanted to know why not i want to be able to put in the score in of the match we had an automatic change the amount of game played, if lost or won and even the goals we made

http://www.megaupload.com/?d=T7NLTU8A

Thank you very much

 

[rosenbe]

I think one of the easiest ways of getting this working would be to have two sets of three corresponding columns, Won, Lose and Draw where if Won appears in column D it would have a value of 1 in the corresponding Won column for that row and if Won appeared in column H the 1 value would appear in the second Won column. And it would work the same for Lose and Draw.

Example:
[First Won Column]
=IF(D1="Won",1,0)
[First Lose Column]
=IF(D1="Lose",1,0)
[First Draw Column]
=IF(D1="Draw",1,0)
...

Then we can use the SUMIF function in the same manner as you are trying to use COUNT. That is, assuming our additional Columns are AA:AG:
[Number Of Wins]
=SUMIF($C$1:$C$37,$K10,AA$1:AA$37)+SUMIF($C$1:$C$37,$K10,AD$1:AD$37)

Hope this helps.

 

[fromspenny]

Excel Workbook
	K	L	M	N	O	P	Q	R	S
9	GROUP A	P	W	L	D	GF	GA	GD	Pts
10	FC Barcelona	10	6	3	1	16	13	3	19
11	Real Madrid	10	6	5	-1	15	12	3	17
12	Manchester United	10	3	5	2	12	19	-7	11
13	Manchester City	10	6	6	-2	18	8	10	16
14	Bayern Munich	10	3	4	3	15	20	-5	12
15	Chelsea	10	3	5	2	11	15	-4	11
Sheet6

 

[Peter_SSs]

fromspenny

You may be unaware that you can use the ‘Analyse range (Forum)’ field near the top left of the Excel jeanie screen to restrict the number of formulas generated. In that field, you can use Ctrl+Click/Drag to select multiple disjoint ranges if required. There is generally no need to display multiple formulas that are basically the same, it just fills up the board and makes your post and the thread harder to read/navigate. For example, here you could just show the row 10 formulas and explain that they are copied down.

 

[fromspenny]

Sheet6

<table style="font-family:Calibri,Arial; font-size:12pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:128px;"><col style="width:88px;"><col style="width:88px;"><col style="width:88px;"><col style="width:88px;"><col style="width:103px;"><col style="width:88px;"><col style="width:88px;"><col style="width:88px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td>
</td><td>K</td><td>L</td><td>M</td><td>N</td><td>O</td><td>P</td><td>Q</td><td>R</td><td>S</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">9</td><td style="background-color:#ccffcc; font-family:Tahoma; font-size:10pt; text-align:center; ">GROUP A</td><td style="background-color:#ccffcc; font-family:Tahoma; font-size:10pt; text-align:center; ">P</td><td style="background-color:#ccffcc; font-family:Tahoma; font-size:10pt; text-align:center; ">W</td><td style="background-color:#ccffcc; font-family:Tahoma; font-size:10pt; text-align:center; ">L</td><td style="background-color:#ccffcc; font-family:Tahoma; font-size:10pt; text-align:center; ">D</td><td style="background-color:#ccffcc; font-family:Tahoma; font-size:10pt; text-align:center; ">GF</td><td style="background-color:#ccffcc; font-family:Tahoma; font-size:10pt; text-align:center; ">GA</td><td style="background-color:#ccffcc; font-family:Tahoma; font-size:10pt; text-align:center; ">GD</td><td style="background-color:#ccffcc; font-family:Tahoma; font-size:10pt; text-align:center; ">Pts</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">10</td><td style="font-family:Tahoma; font-size:10pt; text-align:left; ">FC Barcelona</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">10</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">6</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">3</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">1</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">16</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">13</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">3</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">19</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">11</td><td style="font-family:Tahoma; font-size:10pt; text-align:left; ">Real Madrid</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">10</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">6</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">5</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">-1</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">15</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">12</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">3</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">17</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">12</td><td style="font-family:Tahoma; font-size:10pt; text-align:left; ">Manchester United</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">10</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">3</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">5</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">2</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">12</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">19</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">-7</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">11</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">13</td><td style="font-family:Tahoma; font-size:10pt; text-align:left; ">Manchester City</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">10</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">6</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">6</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">-2</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">18</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">8</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">10</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">16</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">14</td><td style="font-family:Tahoma; font-size:10pt; text-align:left; ">Bayern Munich</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">10</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">3</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">4</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">3</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">15</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">20</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">-5</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">12</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">15</td><td style="font-family:Tahoma; font-size:10pt; text-align:left; ">Chelsea</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">10</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">3</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">5</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">2</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">11</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">15</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">-4</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">11</td></tr></tbody></table>
<table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tbody><tr><td>Spreadsheet Formulas</td></tr><tr><td><table style="font-family:Arial; font-size:9pt;" border="1" cellpadding="2" cellspacing="0"><tbody><tr style="background-color:#cacaca; font-size:10pt;"><td>Cell</td><td>Formula</td></tr><tr><td>L10</td><td>=SUMPRODUCT(--($C$8:$C$37=K10))+SUMPRODUCT(--($G$8:$G$37=K10))</td></tr><tr><td>M10</td><td>=SUMPRODUCT(--($C$8:$C$37=K10),--($E$8:$E$37>$F$8:$F$37))+SUMPRODUCT(--($G$8:$G$37=K10),--($F$8:$F$37>$E$8:$E$37))</td></tr><tr><td>N10</td><td>=SUMPRODUCT(--($C$8:$C$37=K10),--($E$8:$E$37<$F$8:$F$37))+SUMPRODUCT(--($G$8:$G$37=K10),--($E$8:$E$37<$F$8:$F$37))</td></tr><tr><td>O10</td><td>=L10-SUM(M10:N10)</td></tr><tr><td>P10</td><td>=SUMPRODUCT(--($C$8:$C$37=K10),$E$8:$E$37)+SUMPRODUCT(--($G$8:$G$37=K10),$F$8:$F$37)</td></tr><tr><td>Q10</td><td>=SUMPRODUCT(--($C$8:$C$37=K10),$F$8:$F$37)+SUMPRODUCT(--($G$8:$G$37=K10),$E$8:$E$37)</td></tr><tr><td>R10</td><td>=P10-Q10</td></tr><tr><td>S10</td><td>=(M10*3)+(O10*1)</td></tr></tbody></table></td></tr></tbody></table>
Enter formulas in respective cells and copy down

A big thank you to Peter SSs
Excel tables to the web >> Excel Jeanie HTML 4

 

[albri98]

Sheet6

<table style="font-family:Calibri,Arial; font-size:12pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:128px;"><col style="width:88px;"><col style="width:88px;"><col style="width:88px;"><col style="width:88px;"><col style="width:103px;"><col style="width:88px;"><col style="width:88px;"><col style="width:88px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td>
</td><td>K</td><td>L</td><td>M</td><td>N</td><td>O</td><td>P</td><td>Q</td><td>R</td><td>S</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">9</td><td style="background-color:#ccffcc; font-family:Tahoma; font-size:10pt; text-align:center; ">GROUP A</td><td style="background-color:#ccffcc; font-family:Tahoma; font-size:10pt; text-align:center; ">P</td><td style="background-color:#ccffcc; font-family:Tahoma; font-size:10pt; text-align:center; ">W</td><td style="background-color:#ccffcc; font-family:Tahoma; font-size:10pt; text-align:center; ">L</td><td style="background-color:#ccffcc; font-family:Tahoma; font-size:10pt; text-align:center; ">D</td><td style="background-color:#ccffcc; font-family:Tahoma; font-size:10pt; text-align:center; ">GF</td><td style="background-color:#ccffcc; font-family:Tahoma; font-size:10pt; text-align:center; ">GA</td><td style="background-color:#ccffcc; font-family:Tahoma; font-size:10pt; text-align:center; ">GD</td><td style="background-color:#ccffcc; font-family:Tahoma; font-size:10pt; text-align:center; ">Pts</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">10</td><td style="font-family:Tahoma; font-size:10pt; text-align:left; ">FC Barcelona</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">10</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">6</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">3</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">1</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">16</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">13</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">3</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">19</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">11</td><td style="font-family:Tahoma; font-size:10pt; text-align:left; ">Real Madrid</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">10</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">6</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">5</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">-1</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">15</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">12</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">3</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">17</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">12</td><td style="font-family:Tahoma; font-size:10pt; text-align:left; ">Manchester United</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">10</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">3</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">5</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">2</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">12</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">19</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">-7</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">11</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">13</td><td style="font-family:Tahoma; font-size:10pt; text-align:left; ">Manchester City</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">10</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">6</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">6</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">-2</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">18</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">8</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">10</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">16</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">14</td><td style="font-family:Tahoma; font-size:10pt; text-align:left; ">Bayern Munich</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">10</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">3</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">4</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">3</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">15</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">20</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">-5</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">12</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">15</td><td style="font-family:Tahoma; font-size:10pt; text-align:left; ">Chelsea</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">10</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">3</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">5</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">2</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">11</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">15</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">-4</td><td style="font-family:Tahoma; font-size:10pt; text-align:right; ">11</td></tr></tbody></table>
<table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tbody><tr><td>Spreadsheet Formulas</td></tr><tr><td><table style="font-family:Arial; font-size:9pt;" border="1" cellpadding="2" cellspacing="0"><tbody><tr style="background-color:#cacaca; font-size:10pt;"><td>Cell</td><td>Formula</td></tr><tr><td>L10</td><td>=SUMPRODUCT(--($C$8:$C$37=K10))+SUMPRODUCT(--($G$8:$G$37=K10))</td></tr><tr><td>M10</td><td>=SUMPRODUCT(--($C$8:$C$37=K10),--($E$8:$E$37>$F$8:$F$37))+SUMPRODUCT(--($G$8:$G$37=K10),--($F$8:$F$37>$E$8:$E$37))</td></tr><tr><td>N10</td><td>=SUMPRODUCT(--($C$8:$C$37=K10),--($E$8:$E$37<$F$8:$F$37))+SUMPRODUCT(--($G$8:$G$37=K10),--($E$8:$E$37<$F$8:$F$37))</td></tr><tr><td>O10</td><td>=L10-SUM(M10:N10)</td></tr><tr><td>P10</td><td>=SUMPRODUCT(--($C$8:$C$37=K10),$E$8:$E$37)+SUMPRODUCT(--($G$8:$G$37=K10),$F$8:$F$37)</td></tr><tr><td>Q10</td><td>=SUMPRODUCT(--($C$8:$C$37=K10),$F$8:$F$37)+SUMPRODUCT(--($G$8:$G$37=K10),$E$8:$E$37)</td></tr><tr><td>R10</td><td>=P10-Q10</td></tr><tr><td>S10</td><td>=(M10*3)+(O10*1)</td></tr></tbody></table></td></tr></tbody></table>
Enter formulas in respective cells and copy down

A big thank you to Peter SSs
Excel tables to the web >> Excel Jeanie HTML 4

This awesome thank you very much!!! but i have a question if i wanted only to display a game played on Cell L10:L15 only if i put in a score in the to corresponding cells how would i do that. For example we've only played like 8 games so far so the rest of the socres would be empty how do i do it so it displays a game plaid only if there is a score put in the cells? All of the otehr cells are perfect. i tried using the equation on M10 and the doing >,=,< but even if the cell is empty it would still be any of those and it didn't work.

Thank you again for all this honestly this is truly amazing to have this much knowledge to do this of the top of your head thanks again

 

[fromspenny]

In L10 enter =SUMPRODUCT(--($C$8:$C$37=K10),--($E$8:$E$37<>""))+SUMPRODUCT(--($G$8:$G$37=K10),--($F$8:$F$37<>"")) and copy down

 

[fromspenny]

By the way noticed an error in my formula N10 should be =SUMPRODUCT(--($C$8:$C$37=K10),--($E$8:$E$37<$F$8:$F$37))+SUMPRODUCT(--($G$8:$G$37=K10),--($F$8:$F$37<$E$8:$E$37)) and copied down - Sorry about that