Using PowerPivot/DAX or other Tools to Replace Complicated SUMIF Scheme

[djl295]

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.

[CK76]

Is there a way to preform DAX calculations within a normal spreadsheet

No, unfortunately. It will require PowerPivot add-in.

There are several ways to work around it. In order of my personal preference...
1. Leverage PowerQuery and write custom "M" (i.e. PowerQuery) function

You can pass parameter using Table, Named Range etc.
Most, if not all, aggregation can be performed in PowerQuery before loading data to data model/table.
Easy GUI to work with.
It may take some getting used to syntax for "M".

2. VBA code - ADODB.Connection & Recordset

It's pretty easy to work with, if you know basics of VBA and SQL.
Easily able to pass parameter as needed.
There are few easy errors that can be made (Ex. Failing to deal with alphanumeric mixed column; Moving violation within Recordset; Endless Loop etc)

3. MS Query - SQL Command Text
Much like SQL Server, but with some limitations etc.
Will have issue dealing with alphanumeric mixed column (MS Query will auto-select text/numeric type based on first 8 rows of a given column).
May need VBA to pass parameter and/or update connection/Command Text.
Very outdated GUI.

You can also use variable array and/or data collection (Scripting.Dictionary, Collection, ArrayList etc) in VBA code to perform aggregation/transformation.
But I don't recommend it, unless you are familiar with limitations and best practices of this method.