 # Count ifs last 4 or 5 results

• Hi what i´m trying to do is counting how many times a club win, draw or loose, but i only want the last 4 results, not the whole count: i will place here an example:

This Formula is asking to count (A:A) DATE < DATE IN CELL A1;
(B:B) TEAM = TEAM IN CELL B1
(L:L) WIN DRAW OR LOOSE

This formula will tell me how many times that team have won before that date, but i only want the last 4,5 or 6 results.

Can someone tell me what to do. Thanks in advance.

• Re: Count ifs last 4 or 5 results

Do you mean you want to look at the last 4 times that column B is equal to the team in B1 or the last 4 entries in the entire column irregardless of the team?

Where there is a will there are many ways. Finding one that works for you is the challenge!

• Re: Count ifs last 4 or 5 results

No. The result of that formula will say for example that Real Madrid Won 56 Times before that date, what i want is how many games they won on the last 4 games they played before that date not the whole results of a table.

• Re: Count ifs last 4 or 5 results

Thank you NBVC, i took a look and i think it won´t fit my needs i will post my example table and you will see what i mean.

forum.ozgrid.com/index.php?attachment/72611/

Imagine in row 285 Team Boavista have the following results playing at home 5 wins 4 draws and 6 lost games

What i want to know is the last 4 results that should give the following results: 2 wins 1 draw and 1 lost game.

## Files

• Re: Count ifs last 4 or 5 results

Try this *Array formula (for finding "H" in column K):

[COLOR="#0000FF"]=IFERROR(COUNTIFS(INDEX(B\$1:B61,LARGE(IF((A\$1:A61<A61)*(B\$1:B61=B61),ROW(A\$1:A61)-ROW(A\$1)+1),4)):B61,B61,INDEX(A\$1:A61,LARGE(IF((A\$1:A61<A61)*(B\$1:B61=B61),ROW(A\$1:A61)-ROW(A\$1)+1),4)):A61,"<"&A61,INDEX(K\$1:K61,LARGE(IF((A\$1:A61<A61)*(B\$1:B61=B61),ROW(A\$1:A61)-ROW(A\$1)+1),4)):K61,"H"),0)[/COLOR]

Assuming your language is Portuguese (from the attachment), maybe:
[COLOR="#0000FF"]
=SE.ERRO(CONTAR.SE.S(ÍNDICE(B\$1:B61;MAIOR(SE((A\$1:A61<A61)*(B\$1:B61=B61);LIN(A\$1:A61)-LIN(A\$1)+1);4)):B61;B61;ÍNDICE(A\$1:A61;MAIOR(SE((A\$1:A61<A61)*(B\$1:B61=B61);LIN(A\$1:A61)-LIN(A\$1)+1);4)):A61;"<"&A61;ÍNDICE(K\$1:K61;MAIOR(SE((A\$1:A61<A61)*(B\$1:B61=B61);LIN(A\$1:A61)-LIN(A\$1)+1);4)):K61;"H");0)[/COLOR]

[arf]*[/arf]

copied down

Where there is a will there are many ways. Finding one that works for you is the challenge!

• Re: Count ifs last 4 or 5 results

forum.ozgrid.com/index.php?attachment/72613/
Thank you NBVC. Ok i´ve tried, and it´s not working. Im trying to understand the formula, to see where is the error.
I´ve placed the formula in column S (filled in red) and as you can see the results are wrong, and in the last rows they all show a result of 0.
for example in row 130 team Benfica Won the last 4 games at home before that date, and the result showing with this formula is 1.
Is am i doing something wrong? Thanks in advance.

## Files

• Re: Count ifs last 4 or 5 results

Ok i think i´ve fixed. I´m going to try now.

• Re: Count ifs last 4 or 5 results

Ok the formula for the first row is this:

=SE.ERRO(CONTAR.SE.S(ÍNDICE(B\$1:B1;MAIOR(SE((A\$1:A1<A1)*(B\$1:B1=B1);LIN(A\$1:A1)-LIN(A\$1)+1);4)):B1;B1;ÍNDICE(A\$1:A1;MAIOR(SE((A\$1:A1<A1)*(B\$1:B1=B1);LIN(A\$1:A1)-LIN(A\$1)+1);4)):A1;"<"&A1;ÍNDICE(K\$1:K1;MAIOR(SE((A\$1:A1<A1)*(B\$1:B1=B1);LIN(A\$1:A1)-LIN(A\$1)+1);4)):K1;"H");0)

and i was puting the formula you posted in row 1.

Thanks a MILLION NBVC.

One more question how can i do to ask for the last 4 games of a team independently she is playing home or away what do i need to change in the code?
Thanks one more time. Amazing job.

• Re: Count ifs last 4 or 5 results

I am trying to change the code to ask the last 4 games of a team independetly she is playing home or away but its not working.

=SE.ERRO(CONTAR.SE.S(ÍNDICE(B\$1:C199;MAIOR(SE((A\$1:A199<A199)*(B\$1:C199=B199);LIN(A\$1:A199)-LIN(A\$1)+1);4)):B199;B199;ÍNDICE(A\$1:A199;MAIOR(SE((A\$1:A199<A199)*(B\$1:C199=B199);LIN(A\$1:A199)-LIN(A\$1)+1);4)):A199;"<"&A199;ÍNDICE(K\$1:K199;MAIOR(SE((A\$1:A199<A199)*(B\$1:C199=B199);LIN(A\$1:A199)-LIN(A\$1)+1);4)):K199;"H");0)

This is the code i modified to get the results but is not working. Where is the fail?

• Re: Count ifs last 4 or 5 results

is column K relevant for column C?

Where there is a will there are many ways. Finding one that works for you is the challenge!

• Re: Count ifs last 4 or 5 results

This one is not working either, i can´t see where the problem is:((

=SE.ERRO(CONTAR.SE.S(ÍNDICE(C\$1:C305;MAIOR(SE((A\$1:A305<A305)*OU((B\$1:B305=B305);(C\$1:C305=B305));LIN(A\$1:A305)-LIN(A\$1)+1);4)):C305;B305;ÍNDICE(Q\$1:Q305;MAIOR(SE((A\$1:A305<A305)*OU((B\$1:B305=B305);(C\$1:C305=B305));LIN(A\$1:A305)-LIN(A\$1)+1);4)):Q305;Q305;ÍNDICE(B\$1:B305;MAIOR(SE((A\$1:A305<A305)*OU((B\$1:B305=B305);(C\$1:C305=B305));LIN(A\$1:A305)-LIN(A\$1)+1);4)):B305;B305;ÍNDICE(A\$1:A305;MAIOR(SE((A\$1:A305<A305)*OU((B\$1:B305=B305);(C\$1:C305=B305));LIN(A\$1:A305)-LIN(A\$1)+1);4)):A305;"<"&A305;ÍNDICE(K\$1:K305;MAIOR(SE((A\$1:A305<A305)*OU((B\$1:B305=B305);(C\$1:C305=B305));LIN(A\$1:A305)-LIN(A\$1)+1);4)):K305;"H");0)

• Re: Count ifs last 4 or 5 results

Quote from NBVC;793226

is column K relevant for column C?

Yes, i want to know the exact same thing if he won draw or loose. What i am doing is finding the team in column B in Column C.

• Re: Count ifs last 4 or 5 results

But if the team from column B is in column C, then the value in K is not relevant since it is the result for the team in column B in the same row, isn't it? It would be the opposite result (except if a draw).

If instead we use columns L and M for respective teams in A and B, then perhaps this *Array formula in S2 copied down for "WIN":

[COLOR="#0000FF"]=IFERROR(COUNTIFS(INDEX(B\$1:B2,LARGE(IF((A\$1:A2<A2)*((B\$1:B2=B2)+(C\$1:C2=C2)),ROW(A\$1:A2)-ROW(A\$1)+1),4)):B2,B2,INDEX(A\$1:A2,LARGE(IF((A\$1:A2<A2)*((B\$1:B2=B2)+(C\$1:C2=C2)),ROW(A\$1:A2)-ROW(A\$1)+1),4)):A2,"<"&A2,INDEX(L\$1:L2,LARGE(IF((A\$1:A2<A2)*((B\$1:B2=B2)+(C\$1:C2=C2)),ROW(A\$1:A2)-ROW(A\$1)+1),4)):L2,"WIN")+COUNTIFS(INDEX(C\$1:C2,LARGE(IF((A\$1:A2<A2)*((B\$1:B2=B2)+(C\$1:C2=C2)),ROW(A\$1:A2)-ROW(A\$1)+1),4)):C2,B2,INDEX(A\$1:A2,LARGE(IF((A\$1:A2<A2)*((B\$1:B2=B2)+(C\$1:C2=C2)),ROW(A\$1:A2)-ROW(A\$1)+1),4)):A2,"<"&A2,INDEX(M\$1:M2,LARGE(IF((A\$1:A2<A2)*((B\$1:B2=B2)+(C\$1:C2=C2)),ROW(A\$1:A2)-ROW(A\$1)+1),4)):M2,"WIN"),0)[/COLOR]

or

[COLOR="#0000FF"]=SE.ERRO(CONTAR.SE.S(ÍNDICE(B\$1:B2;MAIOR(SE((A\$1:A2<A2)*((B\$1:B2=B2)+(C\$1:C2=C2));LIN(A\$1:A2)-LIN(A\$1)+1);4)):B2;B2;ÍNDICE(A\$1:A2;MAIOR(SE((A\$1:A2<A2)*((B\$1:B2=B2)+(C\$1:C2=C2));LIN(A\$1:A2)-LIN(A\$1)+1);4)):A2;"<"&A2;ÍNDICE(L\$1:L2;MAIOR(SE((A\$1:A2<A2)*((B\$1:B2=B2)+(C\$1:C2=C2));LIN(A\$1:A2)-LIN(A\$1)+1);4)):L2;"WIN")+CONTAR.SE.S(ÍNDICE(C\$1:C2;MAIOR(SE((A\$1:A2<A2)*((B\$1:B2=B2)+(C\$1:C2=C2));LIN(A\$1:A2)-LIN(A\$1)+1);4)):C2;B2;ÍNDICE(A\$1:A2;MAIOR(SE((A\$1:A2<A2)*((B\$1:B2=B2)+(C\$1:C2=C2));LIN(A\$1:A2)-LIN(A\$1)+1);4)):A2;"<"&A2;ÍNDICE(M\$1:M2;MAIOR(SE((A\$1:A2<A2)*((B\$1:B2=B2)+(C\$1:C2=C2));LIN(A\$1:A2)-LIN(A\$1)+1);4)):M2;"WIN");0)[/COLOR]

[arf]*[/arf]

Where there is a will there are many ways. Finding one that works for you is the challenge!

• Re: Count ifs last 4 or 5 results

THANK YOU A TRILLION TIMES.

That worked fine • Re: Count ifs last 4 or 5 results