Forum Discussion
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))))
- Harun24HRBronze 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))))
- alpertakciCopper ContributorThanks 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.
- mazamsezerCopper 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?
- OliverScheurichGold Contributor
=SUMPRODUCT((C20=Annual_Sales[Sales_Executive])*($D$18=Annual_Sales[[#Headers],[January]:[December]])*Annual_Sales[[January]:[December]])
You can try SUMPRODUCT.
- alpertakciCopper ContributorThank 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!