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)) ) )
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.