SOLVED

Formulas and functions

Copper Contributor

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?

8 Replies

Hi @NathanRodrigues 

 

you could use the Subtotal function:

Martin_Weiss_0-1664895732948.png

The only important thing is, that your data is sorted by the column that you want to group, in your case the "Clients" column.

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

Hi @NathanRodrigues 

 

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. 

@NathanRodrigues 

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

image.png

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.

 

@Martin_Weiss 

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

image.png

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

@NathanRodrigues 

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

 

thanks y'all, the SUM(FILTER()) solved my problem :grinning_face_with_sweat:
1 best response

Accepted Solutions
best response confirmed by Hans Vogelaar (MVP)
Solution

@NathanRodrigues 

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

 

View solution in original post