SOLVED

New Contributor

# Formulas and functions

Hello! I need help to agroup the names values in filter formula

So i have this table

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?

8 Replies

# Re: Formulas and functions

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.

# Re: Formulas and functions

I think will not work, because i don't a have table. This table is from a Filter formula.

# Re: Formulas and functions

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.

# Re: Formulas and functions

I do not fully understand your objectives.  If it is simply to return totals for each client, then

``````= LET(
distinct,  UNIQUE(Clients),
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.

# Re: Formulas and functions

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),
)``````

# Re: Formulas and functions

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
best response confirmed by Hans Vogelaar (MVP)
Solution

# Re: 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(
)``````

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,