Excel calculations across several sheets

I'd be inclined to put all your data on one sheet and analyse with a Pivot Table and Slicers.

One suggestion is attached.

Any of the teams can play in all the leagues at any time so the formulas in the "calculations" worksheet should link all the worksheets i.e. premier league, championship, league one and league two.

See attached: completed for "Manchester United" and "Coventry".

Formula all of form ..

=SUMPRODUCT(COUNTIF(INDIRECT("'"&Sheets&"'!$D:$D"),$B20))

with COUNTIFS/SUMIFS as needed

Named range "Sheets" is list of tabs.

And I used the "helper" columns created by Richard in his post #2).

Originally Posted by Onditi

Thanks Richard, but the data has to be separated as it is and all math be done in the "calculations" sheet - there is a reason for that. I will also appreciate a formula that will do calculations for all the teams. I believe this can be achieved using sumproduct and sumif functions.

It's still advisable to keep all your data on a single sheet whether you use a PT or not. Unless you do this you will have to concatenate four COUNTIFS/SUMIFS formula together.

I've added a couple of rows of SUMIFS() and COUNTIFS formula. Note that spelling must be consistent. B11 for instance was originally Mcr Utd and not the full spelling that you had on the data sheets.

And I think your example in row 4 is wrong. Those numbers are Manchester United not Manchester City.

See attached

You could try defining the ranges rather have full columns. Normally with COUNTIF etc you normally don't need to do this, but as SUMPRODUCT is in the formula it may have any impact as with SUMPRODUCT you normally use a defined range.

And the use of INDIRECT also has impact.

You could avoid SUMPRODUCT by defining individual formulae and then adding these e.g =COUNTIF("Premier! .....)+COUNTIf(Championship!......)

TESTED: changing the ranges from D:D, for example, to D2:D1000, significantly improved performance. (suggesting SUMPRODUCT is the "culprit"!).

So I recommend you change all ranges D to L to a finite range