Dynamic filter condition of a filtered dynamic array

[alpertakci]

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!

[#2]

@alpertakci 

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

You can try SUMPRODUCT.

 

[#3 · Accepted 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))))

[#4]

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!

[#5]

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.

[#6]

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?