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))))
=SUMPRODUCT((C20=Annual_Sales[Sales_Executive])*($D$18=Annual_Sales[[#Headers],[January]:[December]])*Annual_Sales[[January]:[December]])You can try SUMPRODUCT.
- 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!
