Forum Discussion
Formulas and functions
We could help with VBA though it is becoming less important to the development of Excel solutions. That said, the solutions I have put forward are all worksheet formulas. Cell references (e.g. $C$11:$G$41) do not appear because I use Defined Names (see Formula ribbon tab). The LET function is one of a small number of Excel functions that has an alternating pattern of parameters (in that case a variable name followed by a formula). I separate the parameter pairs by using Alt/Enter which inserts a new line and gives the function a code-like appearance.
I suggest you look at the workbook I enclosed and look up the functions used there. You are correct that the FILTER function could be used.
= SUM( FILTER(QtyBuy, Clients="Client Marcos") )but SUMIFS is better when it comes to building the results as an array. To obtain the results as a dynamic array using FILTER would require
= MAP(UNIQUE(Clients), LAMBDA(aClient, SUM(FILTER(QtyBuy, Clients=aClient)) ) )
you could use the Subtotal function:
The only important thing is, that your data is sorted by the column that you want to group, in your case the "Clients" column.
Just playing now! A more complex formula giving an extended solution
= LET(
distinct, UNIQUE(HSTACK(Week,Clients)),
wk, TAKE(distinct,,1),
client, TAKE(distinct,,-1),
subtotals, SUMIFS(QtyBuy, Clients, client, Week, wk),
result, HSTACK(distinct, subtotals),
VSTACK(header, result)
)