I am building a system that eliminates data entry for my company by mapping P/L's together and using SUMIF/SUMPRODUCT functions to move data from trial balances to existing financial models. Giving the old models incredible flexibility in allowing the user to chose parameters around the data they would like to view (determined by conditionals in the SUMIF functions). The eventual goal is to move to a product such as SQL server to keep the files small and the data centralized. In the mean time, I am looking for a way to optimize the current functionality. I started by resetting used ranges and converting to xlsb, resulting in a size decrease of 75 MB to 25 MB.

Now, I am looking for an alternative to using the SUMIF functions as they are heavy and slow calculating. I had been reading up about PowerPivot and DAX functions which seem to accomplish the same goal very quickly. However, due to the size of the model and the knowledge of the end-user, I need to keep the models working in the standard excel environment. Is there a way to preform DAX calculations within a normal spreadsheet and not a PivotTable? If not, are there any faster alternatives for preforming SQL-style data aggregation based on user defined parameters without using SUMIFs?

Thank you so much in advance.