Which Formulas to use for Huge Calculations

[toci]

Hi All,

As I said in Title I don't know how to resolve my problem as I have u huge DATA BASE (thousands of rows and about 50 Columns).

In this example you can see that I have 3 conditions to fill the data (Year, Division and Tier) and it's not the problem to pull the data from DATA BASE with SUMPRODUCT function but the problem is that if I put SUMPRODUCT function in e.g 4 Sheets, Excel needs more then 10 minutes to Refresh it. Can you imagine how it looks like in 20 Sheets as I have almost 50 Sheets for this type of report.

I've tried with involving Pivot Tables for this purpose with INDEX+MATCH, but they are only increasing the number of Sheets. On the other hand it works faster then SUMPRODUCT.
Moreover, I don't know how to implement SUMPRODUCT with Pivot Table, specially when my criteria is below each other like in this example.

So, does anybody has some normal solution for this?

Thanks folks!

[protonLeah]

(How to) Upload a Workbook directly to the Forum
(please do not post pictures or links to worksheets)

• Click Advanced (next to quick post),
• Scroll down until you see "Manage Attachments",
• Click that then select "add files" (top right corner).
• Click "Select Files" find your file, click "open" click "upload"
• Once the upload is completed the file name will appear below the input boxes in this window.
• Click "Done" at bottom right to close the Attachment Manager.
• Click "Submit Reply"

Note: Please do not attach password protected workbooks/worksheets
Ensure to disable any Workbook Open/Autorun macros before attaching!

[toci]

(How to) Upload a Workbook directly to the Forum
(please do not post pictures or links to worksheets)

• Click Advanced (next to quick post),
• Scroll down until you see "Manage Attachments",
• Click that then select "add files" (top right corner).
  
  
• Click "Select Files" find your file, click "open" click "upload"
• Once the upload is completed the file name will appear below the input boxes in this window.
• Click "Done" at bottom right to close the Attachment Manager.
• Click "Submit Reply"

Note: Please do not attach password protected workbooks/worksheets
Ensure to disable any Workbook Open/Autorun macros before attaching!

Sorry man, I did exactly as you've described above, and the pictures showed like this!

[JohnTopley]

It is an Excel file "myfile.xlsx" (not image) which is required.

[toci]

Sorry for misunderstood.