Faster way to work with large data?

[ajocius1]

I have list of data with 250 K lines. That data is formatted as table and I regularly update it from another data source. I do the calculation using SUMPRODUCT function to calculate values that I need. Then I copy that cell to 170 cells (10 lines, 17 columns) so that I can see result for every entity and month that I need.

Formula uses several fields to check:

=SUMPRODUCT((Table2[WorkOrder.LegalEntity]=$B6)*(Table2[YearComp]=C$4)*(Table2[MonthCompl]=C$3)*(Table2[Time to complete]))/SUMPRODUCT((Table2[WorkOrder.LegalEntity]=$B6)*(Table2[YearComp]=C$4)*(Table2[MonthCompl]=C$3))

I would like to expand list of KPI's that I can calculate based on same data sheet, but it is already is taking 15-20 sec to recalculate. I have added macros to enable and disable calculation for that result sheet, but still it is time consuming to use it.

I was thinking perhaps there is smarter way of working? Tried Power Query, that helps to import and do simple calculations on source data, but then it returns data into excel sheet Table , so I assume calculations will take the same time. Any thoughts on how can I make my work faster?

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[JEC]

If you split those criteria up into multiple columns, you can get your answer with sumifs probably. That will be faster