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 08:03 AM
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.
Oct 04 2022 01:06 PM
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)
)
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.
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 AM
SolutionWe 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))
)
)
Oct 10 2022 03:23 AM
Oct 05 2022 09:41 AM
SolutionWe 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))
)
)