SOLVED

Dynamic filter condition of a filtered dynamic array

Occasional Contributor

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!

 

 

4 Replies

@alpertakci 

=SUMPRODUCT((C20=Annual_Sales[Sales_Executive])*($D$18=Annual_Sales[[#Headers],[January]:[December]])*Annual_Sales[[January]:[December]])

You can try SUMPRODUCT.

revenue.JPG 

best response confirmed by alpertakci (Occasional Contributor)
Solution

@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_0-1663252405335.png

 

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!
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.