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
1
2
3			home games without a loss	home games without a draw	home games without a win
4
5	Home	Lost
6	Away	Won
7	Home	Won	2	4	1
8	Away	Lost
9	Away	Lost
10	Home	Won
11	Away	Won
12	Home	Lost
13	Away	Lost
14	Home	drew
15	Away	Lost
16

<tbody>
</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))))