Creating a formula for a football stats spreadsheet

mhopps

New Member
Joined
Jan 15, 2014
Messages
2
I’m new here so would like to say Hello all and thank you for reading J

I’m creating a stats spreadsheet for football and have managed so far with all required formulas but this particular one is just killing me

The below formula basically looks at A2 and finds the sheet reference and works out the percentage The home team (data in C2) have scored more than 2.5 goals – This works fine

Code:
=COUNTIFS(INDIRECT("'"&A2&"'!"&"$E$1:$e$55555"),">2.5",INDIRECT("'"&A2&"'!"&"$C$1:$C$55555"),C2)/COUNTIFS(INDIRECT("'"&A2&"'!"&"$C$1:$C$55555"),C2)

The next step was to edit the above formula from ("'"&A2&"'!"&"$E$1:$e$55555"),"> to ("'"&A2&"'!"&"$E$1:$F$55555"),"> so that is sums up the data in “E” and “F” and returned the percentage of games the home team have played with goals greater than 2.5. I’ve edit the above to a SUMIFS function but still can’t crack it

I can upload a sample but not sure I’m allowed to post links here

Any help is appreciated
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

mhopps

New Member
Joined
Jan 15, 2014
Messages
2
Resolved

=SUMPRODUCT(((INDIRECT("'"&A2&"'!"&"$E$2:$E$55555")+INDIRECT("'"&A2&"'!"&"$F$2:$F$55555"))>2.5)*(INDIRECT("'"&A2&"'!"&"$C$2:$C$55555")=C2)/COUNTIFS(INDIRECT("'"&A2&"'!"&"$C$2:$C$55555"),C2))
 

Watch MrExcel Video

Forum statistics

Threads
1,122,519
Messages
5,596,634
Members
414,082
Latest member
sasmita

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top