Getting wrong result

[Patcheen]

Hi Guys and Gals

im trying to calculate how many games a team have won at home without a loss the formula im using is giving me the wrong result.

i want to calculate how many games have a team won or drew without losing the formula im using is

=MAX(FREQUENCY(IF(ISNUMBER(MATCH(J$5:$J$500,{"won","drew"},0)),ROW($J$5:$J$500)),IF($J$5:$J$500="lost",IF(I5:I500="Home",ROW($J$5:$J$500)))))

Column J is the - won drew or lost

Column I is the - Home or away


but as i say its giving me the wrong result.

thanks in advance for all help.

 

[Fluff]

Try

=MAX(FREQUENCY(IF((J5:J500<>"lost")*(I5:I500="home"),ROW($J$5:$J$500)),IF(I5:I500="away",ROW($J$5:$J$500))))

 

[Patcheen]

Thank you Fluff but that only gives the result 1 Thanks again

 

[Fluff]

In that case can you post some sample data along with expected result.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Patcheen]

		I	J
2​	2​	Home	Drew
4​	4​	Away	Drew
4​	4​	Away	Drew
4​	4​	Home	Drew
4​	4​	Away	Drew
0​	3​	Home	Won
1​	3​	Away	Won
5​	5​	Home	Drew
4​	3​	Home	Lost
2​	4​	Away	Won
1​	4​	Away	Won
5​	6​	Home	Won
1​	2​	Away	Won
0​	3​	Home	Won
5​	6​	Away	Won
4​	5​	Home	Won

As you can see at home there was 4 games before losing so the end result should be 4

 

[Fluff]

Thanks for that.
How about

=MAX(FREQUENCY(IF((J5:J500<>"lost")*(I5:I500="home"),ROW($J$5:$J$500)),IF((I5:I500="home")*(J5:J500="lost"),ROW($J$5:$J$500))))

 

[Patcheen]

No Fluff Thank you. You have solved it thank you so very very much your fantastic

 

[Fluff]

You're welcome & thanks for the feedback.