Creating subtotals within an array formula

%3CLINGO-SUB%20id%3D%22lingo-sub-1620392%22%20slang%3D%22en-US%22%3ECreating%20subtotals%20within%20an%20array%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1620392%22%20slang%3D%22en-US%22%3E%3CP%3EI%20need%20to%20create%20subtotals%20of%20data%20spawned%20by%20the%20%3DSORT%20and%20%3DFILTER%20functions.%20I%20can%20do%20it%20the%20old%20fashioned%2C%20hard%2C%20way%20by%20comparing%20the%20value%20in%20the%20current%20row%20against%20the%20most%20previous%20value%20and%20if%20different%20create%20my%20total%20but...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20far%20I've%20not%20found%20a%20way%20to%20create%20the%20subtotal%20within%20the%20array%20formula.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20formula%20is%20below.%20All%20cells%20referenced%20are%20on%20another%20sheet%20and%20are%20dynamic%20and%20liable%20to%20change%20at%20any%20time.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFormula%20is%3A%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DSORT((FILTER(CHOOSE(%7B1%2C2%2C3%2C4%2C5%2C6%2C7%7D%2CTransactions!Account%2CTransactions!Due_Date%2CTransactions!Provider%2CTransactions!Expense_Center%2C%20Transactions!Expense_Sub_Center%2CTransactions!Notes%2CTransactions!Budgeted_Amount)%2C(Transactions!Provider%26lt%3B%26gt%3B%22%22)*(Transactions!Expense_Center%26lt%3B%26gt%3B%22%22)*(Transactions!Category%3D%22Expense%22)*(Transactions!Budgeted_Amount%26lt%3B%26gt%3B%22%22)%2C%22%22))%2C%7B1%2C2%2C3%2C4%2C5%7D)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20sort%20on%20the%20first%20five%20cells%20of%20the%20array%20BUT%20only%20want%20to%20break%20and%20create%20a%20subtotal%20when%20Transactions!Account%20changes.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EA%20suggestion%20here%20would%20be%20greatly%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETheOldPuterMan%20AKA%20John%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1620392%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1620466%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20subtotals%20within%20an%20array%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1620466%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F317593%22%20target%3D%22_blank%22%3E%40TheOldPuterMan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20don't%20have%20the%20Sort%2FFilter%20functions%2C%20so%20I%20can't%20re-create%20your%20scenario%20or%20test%20a%20solution.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%2C%20if%20it%20helps%2C%20this%20formula%20will%20create%20an%20array%20of%20subtotals.%20Perhaps%20you%20can%20incorporate%20it%20or%20see%20if%20it%20gives%20you%20any%20ideas.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Contributor

I need to create subtotals of data spawned by the =SORT and =FILTER functions. I can do it the old fashioned, hard, way by comparing the value in the current row against the most previous value and if different create my total but...

 

So far I've not found a way to create the subtotal within the array formula.

 

The formula is below. All cells referenced are on another sheet and are dynamic and liable to change at any time.

 

Formula is:

=SORT((FILTER(CHOOSE({1,2,3,4,5,6,7},Transactions!Account,Transactions!Due_Date,Transactions!Provider,Transactions!Expense_Center, Transactions!Expense_Sub_Center,Transactions!Notes,Transactions!Budgeted_Amount),(Transactions!Provider<>"")*(Transactions!Expense_Center<>"")*(Transactions!Category="Expense")*(Transactions!Budgeted_Amount<>""),"")),{1,2,3,4,5})

 

I sort on the first five cells of the array BUT only want to break and create a subtotal when Transactions!Account changes.

 

A suggestion here would be greatly appreciated.

 

Thanks,

 

TheOldPuterMan AKA John

1 Reply
Highlighted

@TheOldPuterMan 

 

I don't have the Sort/Filter functions, so I can't re-create your scenario or test a solution.

 

But, if it helps, this formula will create an array of subtotals. Perhaps you can incorporate it or see if it gives you any ideas.