Formulas and functions

New 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:


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. 


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.



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


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),
         SUM(FILTER(QtyBuy, Clients=aClient))


thanks y'all, the SUM(FILTER()) solved my problem