SUMIFS with multiple criteria WITH specified dynamic range criteria

Hello,

For simplicity sake, I've attached a sample. In the sample, I've also given a few scenarios of how the 'dynamic range criteria' will be.

Due to the limitation of SUMIFS (with my current knowledge), the function can only take 1 criteria per column. Thus, if there's many criteria that users like to search in the same column, then there'll need multiple sumifs.

For example, below is the SUMIFS function for 3 criteria on the same column that's from 'dynamic range criteria' with other criterias stays the same.

Please Login or Register  to view this content.

My questions is, is there a way for the SUMIFS to 'know' and have multiple SUMIFS USING the 'dynamic range criteria' that was listed


Edit:

I was able to use it via ARRAY formula

Please Login or Register  to view this content.

where $Y$2:$AA$2 is the 'dynamic range criteria'

i.e. with the 'dynamic range criteria' has values in range B1:D1, then there will be 3 SUMIFS, with each sumifs reflecting, "B", "C" and "D".
or if the 'dynamic range criteria' has values in range B1:F1, then there will be 5 SUMIFS, with each sumifs reflecting "B", "C", "D", "E" and "F".

If not, is there an alternative formula that enables what I'd like above (reflecting the number of 'dynamic range criteria')?