Forum Discussion
Solved
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...
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 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))))
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?
- 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.
