Creating a formula for a football stats spreadsheet

[mhopps]

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

=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

 

[mhopps]

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))