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) )- sure, thank y'all, but i just want to do this in filter formula, is that possible? because i don't know how to use VBA in excel yet
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)) ) )
- I think will not work, because i don't a have table. This table is from a Filter formula.
sorry, that was my mistake. You are right, within FILTER function, you cannot use subtotals or any other form of grouping, because it's a dynamic array.
The only option that I can see is to create a Pivot table. If this is an option at all, this could even make your filter formula obsolete, if you base the Pivot table directly on the source data.
