# Last 6 matches home and away soccer form table

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.

• Re: Last 6 matches home and away soccer form table

Try these formulas:

To get count of scores over 0

=SUM(IF(\$D\$3:\$D\$870=Y3,IF(ROW(\$D\$3:\$D\$870)>=LARGE(IF(\$D\$3:\$D\$870=Y3,ROW(\$D\$3:\$D\$870)),6),IF(\$K\$3:\$K\$870>0,1))))

Note: After you enter the formula in the cell, hold the CTRL key and the SHIFT key together, then press ENTER. You should see { } brackets appear around the formula, then you can copy formula down the column

To get count of no scores:

=SUM(IF(\$D\$3:\$D\$870=Y3,IF(ROW(\$D\$3:\$D\$870)>=LARGE(IF(\$D\$3:\$D\$870=Y3,ROW(\$D\$3:\$D\$870)),6),IF(\$K\$3:\$K\$870=0,1))))

with same way of entering the formula as above.

or even simply =6-Z3 should give same result.

IF you have ITALIAN version of EXCEL, you could use these formulas:

=SOMMA(SE(\$D\$3:\$D\$870=Y3,SE(RIF.RIGA(\$D\$3:\$D\$870)>=GRANDE(SE(\$D\$3:\$D\$870=Y3,RIF.RIGA(\$D\$3:\$D\$870)),6),SE(\$K\$3:\$K\$870>=0,1))))

and

=SOMMA(SE(\$D\$3:\$D\$870=Y3,SE(RIF.RIGA(\$D\$3:\$D\$870)>=GRANDE(SE(\$D\$3:\$D\$870=Y3,RIF.RIGA(\$D\$3:\$D\$870)),6),SE(\$K\$3:\$K\$870=0,1))))

and if you have semi colon separators, instead of commas:

=SOMMA(SE(\$D\$3:\$D\$870=Y3;SE(RIF.RIGA(\$D\$3:\$D\$870)>=GRANDE(SE(\$D\$3:\$D\$870=Y3;RIF.RIGA(\$D\$3:\$D\$870));6);SE(\$K\$3:\$K\$870>=0;1))))

and

=SOMMA(SE(\$D\$3:\$D\$870=Y3;SE(RIF.RIGA(\$D\$3:\$D\$870)>=GRANDE(SE(\$D\$3:\$D\$870=Y3;RIF.RIGA(\$D\$3:\$D\$870));6);SE(\$K\$3:\$K\$870=0;1))))

NB: My range is up to row 870 to cover all Italian teams.... you can extend it further, but with these special Array formulas, you should not cover more than is necessary or it will be less efficient.

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

• Re: Last 6 matches home and away soccer form table

hello mate.

thanks so much. you are the best!!! i don't know how to thanksgive you!!

Now i have a question about some mistakes by counting statistics... perhaps i apologized something:

for example by taking "INTER" in home... if i count Inter last games played in home to calculate "scored Yes", with your formulas, i'll see displayed it with three games in home, but inter played, in last six total games, 4 times in home and two games by away... how to correct and solve this mistake???

i want its last six games in order of the decrescending rounds played... week by week...

I want learn how i can count only the lasts six matches of a football Team "X".
It will play 4 matches in home and 2 away; it had played 3 matches in home and 3 away or it could have played 1 match in home and 5 away... i will want count always and only its six lasts matches to calculate some statistics.

thanks dear,

thanks of all.

take care,
regards.

luciano

• Re: Last 6 matches home and away soccer form table

Ok, I thought you wanted to count their last 6 HOME games....

Try these formulas:

=SUM(IF((\$D\$3:\$D\$870=Y3)+(\$F\$3:\$F\$870=Y3),IF(ROW(\$D\$3:\$D\$870)>=LARGE(IF((\$D\$3:\$D\$870=Y3)+(\$F\$3:\$F\$870=Y3),ROW(\$D\$3:\$D\$870)),6),IF(\$K\$3:\$K\$870>0,1))))

=SUM(IF((\$D\$3:\$D\$870=Y3)+(\$F\$3:\$F\$870=Y3),IF(ROW(\$D\$3:\$D\$870)>=LARGE(IF((\$D\$3:\$D\$870=Y3)+(\$F\$3:\$F\$870=Y3),ROW(\$D\$3:\$D\$870)),6),IF(\$K\$3:\$K\$870=0,1))))

remember to confirm with CTRL+SHIFT+ENTER and copy down

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

• Re: Last 6 matches home and away soccer form table

No dear.

If you see Inter team, in d:d, in last six serie A rounds, inter played 4 games like home and 2 by away.

Otherwise i don't know what and how to expalain the last six games in last six rounds i want how to calculate statistics in last home and away games il last six rounds.

I hope you'll understand me.

Take care
Regards

• Re: Last 6 matches home and away soccer form table

I thought about that just now and was about to post a correction.... you beat me....

try these:
=SUM(IF((\$D\$3:\$D\$870=Y3)+(\$F\$3:\$F\$870=Y3),IF(ROW(\$D\$3:\$D\$870)>=LARGE(IF((\$D\$3:\$D\$870=Y3)+(\$F\$3:\$F\$870=Y3),ROW(\$D\$3:\$D\$870)),6),IF((\$D\$3:\$D\$870=Y3)*(\$K\$3:\$K\$870>0)+(\$F\$3:\$F\$870=Y3)*(\$L\$3:\$L\$870>0),1))))

and
=SUM(IF((\$D\$3:\$D\$870=Y3)+(\$F\$3:\$F\$870=Y3),IF(ROW(\$D\$3:\$D\$870)>=LARGE(IF((\$D\$3:\$D\$870=Y3)+(\$F\$3:\$F\$870=Y3),ROW(\$D\$3:\$D\$870)),6),IF((\$D\$3:\$D\$870=Y3)*(\$K\$3:\$K\$870=0)+(\$F\$3:\$F\$870=Y3)*(\$L\$3:\$L\$870=0),1))))

Note: I resimplified the formulas....

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

• Re: Last 6 matches home and away soccer form table

DEAR

i took error num in z:Z

[Blocked Image: http://i41.tinypic.com/30cqhqq.jpg]

i haven't yet try lasts formulas.

i saw *(\$K\$3:\$K\$870>0)

many thanks dear,

thanks of all.

take care,
regards.

luciano

• Re: Last 6 matches home and away soccer form table

In Italian the last formulas are:

=SOMMA(SE((\$D\$3:\$D\$870=Y3)+(\$F\$3:\$F\$870=Y3);SE(RIF.RIGA(\$D\$3:\$D\$870)>=GRANDE(SE((\$D\$3:\$D\$870=Y3)+(\$F\$3:\$F\$870=Y3);RIF.RIGA(\$D\$3:\$D\$870));6);SE((\$D\$3:\$D\$870=Y3)*(\$K\$3:\$K\$870>0)+(\$F\$3:\$F\$870=Y3)*(\$L\$3:\$L\$870>0);1))))

and

=SOMMA(SE((\$D\$3:\$D\$870=Y3)+(\$F\$3:\$F\$870=Y3);SE(RIF.RIGA(\$D\$3:\$D\$870)>=GRANDE(SE((\$D\$3:\$D\$870=Y3)+(\$F\$3:\$F\$870=Y3);RIF.RIGA(\$D\$3:\$D\$870));6);SE((\$D\$3:\$D\$870=Y3)*(\$K\$3:\$K\$870=0)+(\$F\$3:\$F\$870=Y3)*(\$L\$3:\$L\$870=0);1))))

In your screen shot, I can see that you did not follow my instruction. After you enter the formula, hold the CTRL key and SHIFT keys down, then press ENTER. You will see these bracket { } around the formula. Then you can copy formula down.

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

• Re: Last 6 matches home and away soccer form table

dear last two kinds of formulas give me different results how you can calculate on attacked url file.

The first is good to calculate last six home+away.

Can t we solve lasts my question ???

Have a nice dinner.

See you
Take care.
Luciano

• Re: Last 6 matches home and away soccer form table

Have a look here: https://www.dropbox.com/s/yyv2…e%20You%20solve...-1.xlsm

(note: I removed your first sheet to reduce size, but still too big to upload to Ozgrid)....

I only completed column Z:AA... you can fill in the rest with same idea.

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

• Re: Last 6 matches home and away soccer form table

hi nbvc,

In D column We have Home Teams ------- In F column We have Away Teams!!!
So there are many rows which contain Home and Away Teams… on the right of these,
in Q, R, S, T columns, there are some calculations about their results:
“ score or not, under over goals, both teams score… “

I want count in last six occurences, about last six league rounds, like shown in column B, as follows:

How many times a Home Team has scored or not ( column N) … so this for other Q, R, S, T
How many times an Away Team has scored or not ( column O) … so this for other Q, R, S, T

!!!!!!!!!!!!!!! All this about ONLY LAST SIX LEAGUE ROUNDS !!!!!!!!!!!!!!!!!!!!

For example in Italy Serie A league championship, like in column B,
from the first last round, the 19th, until the sixth last round, the 14th round…
by covering and taking always the last six occurences…
Z9: Inter played _4 games in home_ by scoring 4 times, like shown in Z:Z … ( 4 scored Yes)
Z9: Inter played _2 games away_ by scoring only 1 time and not scoring also 1 time ( 1 scored Yes & 1 Not)

By following these rules I want calculate what I explained, about all the observable league teams in Y:Y

Note Well:
In other leagues, expecially English leagues, when I refresh query web about new league championship results, I find postponed or abandoned games of previous league rounds which are replayed.
These replayed games are assorted, mixed to very last 6 played rounds…
According to my ideas They are calculated like lasts by me… and sometimes, by counting last six occurences of a Team, covering last six league rounds, we should find a Team which played also 5 times like Home and only 1 Away.

Hope I’ll be able to be helped,
I’M STUCK.

TAKE CARE.
Regards.
Luciano

• Re: Last 6 matches home and away soccer form table

Let's try this again....

In Z2:

=SOMMA(SE((\$D\$3:\$D\$870=Y3)+(\$F\$3:\$F\$870=Y3);SE(RIF.RIGA(\$D\$3:\$D\$870)<=PICCOLO(SE((\$D\$3:\$D\$870=Y3)+(\$F\$3:\$F\$870=Y3);RIF.RIGA(\$D\$3:\$D\$870));6);SE((\$D\$3:\$D\$870=Y3)*(\$K\$3:\$K\$870>0);1))))

in AA2:

=SOMMA(SE((\$D\$3:\$D\$870=Y3)+(\$F\$3:\$F\$870=Y3);SE(RIF.RIGA(\$D\$3:\$D\$870)<=PICCOLO(SE((\$D\$3:\$D\$870=Y3)+(\$F\$3:\$F\$870=Y3);RIF.RIGA(\$D\$3:\$D\$870));6);SE((\$D\$3:\$D\$870=Y3)*(\$K\$3:\$K\$870=0);1))))

in AK2:

=SOMMA(SE((\$D\$3:\$D\$870=Y3)+(\$F\$3:\$F\$870=Y3);SE(RIF.RIGA(\$D\$3:\$D\$870)<=PICCOLO(SE((\$D\$3:\$D\$870=Y3)+(\$F\$3:\$F\$870=Y3);RIF.RIGA(\$D\$3:\$D\$870));6);SE((\$F\$3:\$F\$870=Y3)*(\$L\$3:\$L\$870>0);1))))

in AL2:

=SOMMA(SE((\$D\$3:\$D\$870=Y3)+(\$F\$3:\$F\$870=Y3);SE(RIF.RIGA(\$D\$3:\$D\$870)<=PICCOLO(SE((\$D\$3:\$D\$870=Y3)+(\$F\$3:\$F\$870=Y3);RIF.RIGA(\$D\$3:\$D\$870));6);SE((\$F\$3:\$F\$870=Y3)*(\$L\$3:\$L\$870=0);1))))

each formula must be confirmed with CTRL+SHIFT+ENTER keys, so that { } appear around the formulas:

then you can copy each down.

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

• Re: Last 6 matches home and away soccer form table

You are a BOMBBBBBBBBBBBBBBBBBBBBBBBBBB!!!!!!!!!
SOLVED
SOLVED
SOLVED
SOLVED

so now i let me to try the other statistics about columns q r s t ... by taking your formulas!!!

nice to meet you deaaar

muy feliz.

kind regardsss

• Re: Last 6 matches home and away soccer form table

Hello my dear friend,
making even thanks to you for what You have suggested to me,

deleting the calculations in columns NOQRST. ..
to lighten the sheet ...
because these calculations appear to me already in the cells of calculation from Z to CL ...

Can I calculate formulas without my calculations in the form of matrices??

I could help with the cells help? What do you suggest?
Could you give me more advices or straights???

Thank you for everything you have done and will do ...
look forward to hearing from you...

take care;
regards,
luciano.

• Re: Last 6 matches home and away soccer form table

In fact, if you can set fixed ranges to cover only those last 6 rounds e.g. rows 3 to 67 only, then you can simplify with COUNTIFS

In Z3 the formula (in Italian) would now simply be:

=CONTA.PIÙ.SE(\$D\$3:\$D\$67;Y3;\$K\$3:\$K\$67;">0")

In AA3

=CONTA.PIÙ.SE(\$D\$3:\$D\$67;Y3;\$K\$3:\$K\$67;"0")

in AK3

=CONTA.PIÙ.SE(\$F\$3:\$F\$67;Y3;\$L\$3:\$L\$67;">0")

and in AL3:

=CONTA.PIÙ.SE(\$F\$3:\$F\$67;Y3;\$L\$3:\$L\$67;"0")

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

• Re: Last 6 matches home and away soccer form table

Dear friend, about ONLY LAST SIX LEAGUE ROUNDS...

For example in Italy Serie A league championship, like in column B,from the first last round, the 19th, until the sixth last round, the 14th round…by covering and taking always the last six occurences…Z9: Inter played _4 games in home_ by scoring 4 times, like shown in Z:Z … ( 4 scored Yes)

Z9: Inter played _2 games away_ by scoring only 1 time and not scoring also 1 time ( 1 scored Yes & 1 Not)By following these rules I want calculate what I explained, about all the observable league teams in Y:Y

Note Well:In other leagues, expecially English leagues, when I refresh query web about new league championship results, I find postponed or abandoned games of previous league rounds which are replayed.These replayed games are assorted, mixed to very last 6 played rounds…

According to my ideas They are calculated like lasts by me… and sometimes, by counting last six occurences of a Team, covering last six league rounds, we should find a Team which played also 5 times like Home and only 1 Away.

As follows, see in A5027... ENGLISH CONFERENCE...
THERE ARE THREE REPLAYED GAMES THAT I COUNT LIKE LAST SIX OCCURENCES...
SO RAWS CHANGES... AND I MISTAKE CALCULATION... WITH HIDE RAWS..

How can count these as follows... with countif???

In Z2:=SOMMA(SE((\$D\$3:\$D\$870=Y3)+(\$F\$3:\$F\$870=Y3);SE(RIF.RIGA(\$D\$3:\$D\$870)<=PICCOLO(SE((\$D\$3:\$D\$870=Y3)+(\$F\$3:\$F\$870=Y3);RIF.RIGA(\$D\$3:\$D\$870));6);SE((\$D\$3:\$D\$870=Y3)*(\$K\$3:\$K\$870>0);1))))

in AA2:=SOMMA(SE((\$D\$3:\$D\$870=Y3)+(\$F\$3:\$F\$870=Y3);SE(RIF.RIGA(\$D\$3:\$D\$870)<=PICCOLO(SE((\$D\$3:\$D\$870=Y3)+(\$F\$3:\$F\$870=Y3);RIF.RIGA(\$D\$3:\$D\$870));6);SE((\$D\$3:\$D\$870=Y3)*(\$K\$3:\$K\$870=0);1))))

In AK2:=SOMMA(SE((\$D\$3:\$D\$870=Y3)+(\$F\$3:\$F\$870=Y3);SE(RIF.RIGA(\$D\$3:\$D\$870)<=PICCOLO(SE((\$D\$3:\$D\$870=Y3)+(\$F\$3:\$F\$870=Y3);RIF.RIGA(\$D\$3:\$D\$870));6);SE((\$F\$3:\$F\$870=Y3)*(\$L\$3:\$L\$870>0);1))))

in AL2:=SOMMA(SE((\$D\$3:\$D\$870=Y3)+(\$F\$3:\$F\$870=Y3);SE(RIF.RIGA(\$D\$3:\$D\$870)<=PICCOLO(SE((\$D\$3:\$D\$870=Y3)+(\$F\$3:\$F\$870=Y3);RIF.RIGA(\$D\$3:\$D\$870));6);SE((\$F\$3:\$F\$870=Y3)*(\$L\$3:\$L\$870=0);1))))

Thanks 4 all u ll do.
Take care.

Regards

• Re: Last 6 matches home and away soccer form table

I don't understand anything you said here.

Are you saying the COUNTIFS formulas I just gave won't work? Are the original formulas that you quote above working or not?! I don't understand your new question!

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

• Re: Last 6 matches home and away soccer form table

Dear,

the original formulas, under form of arrays, that you quoted above are working, so now i would translate them in simple formulas without under form of arrays.

Exscusme for the mutiple posts, i believe in you and i'll follow only you.

I hope you understand.

Nice to wait for You.

Regards

• Re: Last 6 matches home and away soccer form table

Since you are limiting to only "last 6" out of many more, then it is difficult to not involve the array formulas. You might be able to use Helper columns, but those formulas may either become a little complicated, or you will need multiple helper columns.

The best way to simplify the formula is to use the defined range that only covers the last 6 rounds of each countries teams... (for example again, for Italy, that would be D3:D67). Then you can use the simple COUNTIFS formulas I suggested.

Maybe if you can use column B to determine last 6 (ie. check if round number is less than (19-5... or 14)

e.g. In Z3:

=CONTA.PIÙ.SE(\$B\$3:\$B\$870;>="&\$B\$3-5;\$D\$3:\$D\$870;Y3;\$K\$3:\$K\$870;">0")

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!