Forum Discussion

alpertakci's avatar
alpertakci
Copper Contributor
Sep 15, 2022

Dynamic filter condition of a filtered dynamic array

I am sure this has been answered before but I don't think I am able to compose the correct search query to find it on any support site. To summarize, (I am assuming) I need to use the FILTER function on a dynamic array that has already been generated by a FILTER function so that I can select which columns to show from the originally filtered data.

 

The tricky part (for me) is that I cannot use a "{1,0,0,1}" type of an include argument as the column is dependent on the month selected as shown in the attached file. Is there a way to construct a formula where I can dynamically update the column from where the value is retrieved?

 

Thank you all, in advance, for your support!

 

 

  • alpertakci You nest FILTER() function twice. Try-

    =SUM(FILTER(FILTER(Annual_Sales,Annual_Sales[#Headers]=$D$18),Annual_Sales[Sales_Executive]=C20))

    For dynamic spill array approach use BYROW() function.

    =BYROW(C20:C22,LAMBDA(x,SUM(FILTER(FILTER(Annual_Sales,Annual_Sales[#Headers]=$D$18),Annual_Sales[Sales_Executive]=x))))

     

  • Harun24HR's avatar
    Harun24HR
    Bronze Contributor

    alpertakci You nest FILTER() function twice. Try-

    =SUM(FILTER(FILTER(Annual_Sales,Annual_Sales[#Headers]=$D$18),Annual_Sales[Sales_Executive]=C20))

    For dynamic spill array approach use BYROW() function.

    =BYROW(C20:C22,LAMBDA(x,SUM(FILTER(FILTER(Annual_Sales,Annual_Sales[#Headers]=$D$18),Annual_Sales[Sales_Executive]=x))))

     

    • alpertakci's avatar
      alpertakci
      Copper Contributor
      Thanks very much for the alternate solutions. Once I can convince my SysAd to upgrade my Excel version, I would be able to go for BYROW as it is working like a charm on my personal computer.
    • mazamsezer's avatar
      mazamsezer
      Copper Contributor

      Brilliant answer Harun24HR. What I need is further enhancing this formula:  rather than giving a range of cells in BYROW function's first arguement, I want to pass VALUE(TEXTSPLIT(cells's address,,"_")). When I try this it did'nt calculate. Can you elaborate please?

    • alpertakci's avatar
      alpertakci
      Copper Contributor
      Thank you for the solution. This is a valid solution with one caveat: it would not spill. What I am after is something that would automatically spill along with the Sales Executives, if I were to add a new one to the Sales Executive table, I can make them spill but the SUMPRODUCT formula does not spill along with it. I think Harun24HR has the formula for it. Yet, again, thank you!

Resources