Forum Discussion
Solved
Formulas and functions
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?
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 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) )would give
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.
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
- 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.
