Count ifs last 4 or 5 results

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    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:


    =COUNT.IF.S(A:A;"<"&A1;B:B;B1;L:L;"WIN";AC:AC;AC1;AD:AD;AD1)


    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
    (AC:AC) & (AD:AD) ARE THE FIELD FOR COUNTRY AND LEAGUE


    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.

    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.

    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!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login