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



You can try SUMPRODUCT.


best response confirmed by alpertakci (Occasional Contributor)

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


For dynamic spill array approach use BYROW() function.




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.