Forum Discussion
Help with MMULT for a range containing 100,000+ values
I have been trying to solve a problem for some time now but unable to find an acceptable solution and hence requesting help from you. The attached has a snapshot of what I am trying to achieve. The ...
It all depends on concrete data. I assume we shall sumif 100000 range with > 7500 criteria elements. On my relatively slow PC
=SUMIFS( <100000 rows sum range>, <100000 rows names range>, <10000 rows unique names range> )
took about 15 sec.
I don't think that any formula with dynamic arrays, in general any other formula, will be working faster.
To compare, refresh of Power Query aggregation for the same dataset takes about 4 sec and 2-3 sec more if aggregate 8 columns at once.
Hello Sergei, the actual count I have is 182,052 of which 94,318 are unique which is ~52% of the total records/rows. This is just representative data and is expected to increase over time. the processing is happening on a Dell Latitude 3400 (Intel i5 processor with 16GB RAM) laptop and my experience with SUMIFS on this laptop on the aforesaid data is rather slow compared to dynamic array formulas.
I am trying to avoid PowerQuery since it will require refresh every time data gets changed. This tool will be run periodically by junior / inexperienced resources who might miss out to refresh after data change and in turn generate an incorrect report / dashboard.
The tool being currently developed is for analyzing and reporting weekly Time-Sheet data (resource utilization) to the leadership and is expected to get extended to include invoicing to clients for T&M contracts which will comprise reconciliation of Time-Sheet data with client captured utilization information.
The whole process will contain a series of Excel workbooks, each performing a specific task, in a predefined sequence.
I am trying to avoid PowerQuery since it will require refresh every time data gets changed. This tool will be run periodically by junior / inexperienced resources who might miss out to refresh after data change and in turn generate an incorrect report / dashboard.
The tool being currently developed is for analyzing and reporting weekly Time-Sheet data (resource utilization) to the leadership and is expected to get extended to include invoicing to clients for T&M contracts which will comprise reconciliation of Time-Sheet data with client captured utilization information.
The whole process will contain a series of Excel workbooks, each performing a specific task, in a predefined sequence.