Formula not working

[Patcheen]

Hi guys i have this formula -

=MAX(FREQUENCY(IF(ISNUMBER(MATCH($I$5:$I$200,{"won"},0)),ROW($I$5:$I$200)),IF($I$5:$I$200,"lost","drew"),0),ROW($I$5:$I$200),IF($H$5:$H$200="Home",ROW($I$5:$I$200)))))

when i enter it it says that ive entered too many arguments -- im trying to find how many games played at home without a loss -

which i changed from this (which is working perfectly) -
=MAX(FREQUENCY(IF(ISNUMBER(MATCH($I$5:$I$200,{"drew","won"},0)),ROW($I$5:$I$200)),IF($I$5:$I$200="lost",ROW($I$5:$I$200),IF($H$5:$H$200="Home",ROW($I$5:$I$200)))))


TIA in Advance

 

[Aladin Akyurek]

Care to post a sample of 10 rows along with the conditions and the expected count?

 

[jorismoerings]

In addition to aladin request; my question what would the expected result of the red part of the formula be.

=MAX(FREQUENCY(IF(ISNUMBER(MATCH($I$5:$I$200,{"won"},0)),ROW($I$5:$I$200)),IF($I$5:$I$200,"lost","drew"),0),ROW($I$5:$I$200),IF($H$5:$H$200="Home",ROW($I$5:$I$200)))))

because like you've formulated currently, excel expects TRUE or FALSE input in range I5:I200 while the rest of the formula assumes it contains either Won, Lost or Drew.

 

[Patcheen]

	H	I	S	T	U
Home	Lost
Away	Won
Home	Won
Away	Lost
Away	Lost
Home	Won
Away	Won
Home	Lost
Away	Lost
Home	drew
Away	Lost

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]home games without a loss[/TD]
[TD="align: center"]home games without a draw[/TD]
[TD="align: center"]home games without a win[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]5[/TD]

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]6[/TD]

[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]1[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

​

hope that helps its a rough idea

 

[Aladin Akyurek]

Is home games without a win equal 1?

 

[Patcheen]

yes as far as i know

 

[Aladin Akyurek]

yes as far as i know

Well, I count 3 home games without a win...

 

[Patcheen]

your right it is indeed 3 my apologies

 

[Aladin Akyurek]

your right it is indeed 3 my apologies

1. Control+shift+enter, not just enter:

=SUM(FREQUENCY(IF($A$5:$A$15="home",IF($B$5:$B$15="won",IF(1-($B$5:$B$15="lost"),ROW($A$5:$A$15)))),IF(1-($A$5:$A$15="home"),ROW($A$5:$A$15))))

2. Control+shift+enter, not just enter:

=SUM(FREQUENCY(IF($A$5:$A$15="home",IF(1-($B$5:$B$15="drew"),ROW($A$5:$A$15))),IF(1-($A$5:$A$15="home"),ROW($A$5:$A$15))))

3. Control+shift+enter, not just enter:

=SUM(FREQUENCY(IF($A$5:$A$15="home",IF(1-($B$5:$B$15="won"),ROW($A$5:$A$15))),IF(1-($A$5:$A$15="home"),ROW($A$5:$A$15))))