Oct 03 2022 12:54 PM
Oct 03 2022 12:54 PM
Hello! I need help to agroup the names values in filter formula
So i have this table
Week / Clients /Qty Buy
40 / Client Marcos /5
40 / Client Marcos /2
40 / Client Yago /5
I want to group the client Marcos in 1 filter and sum the total of each clients
Anyone can help me with this?
Oct 04 2022 11:06 PM
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.
Oct 05 2022 03:06 AM
I do not fully understand your objectives. If it is simply to return totals for each client, then
= LET( distinct, UNIQUE(Clients), subtotals, SUMIFS(QtyBuy, Clients, distinct), HSTACK(distinct, subtotals) )
More concise, but less informative, you could return the numbers alone using
= SUMIFS(QtyBuy, Clients, UNIQUE(Clients))
If you want to filter by week as well as client, it is not a problem. It is just that the formulas grow according to the complexity of the task.
Oct 05 2022 03:28 AM
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) )
Oct 05 2022 04:09 AM
Oct 05 2022 09:41 AMSolution
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)) ) )