How can I SUMIF with horizontal and vertical criteria (including dates)?

Question

I'm trying to SUMIF data based on two criteria:

1. The title of the column (horizontal)
2. In between two dates (vertical)

I have tried a number of functions to solve this problem but to no avail.

I have tried functions with Match, Index, and Filter, but I couldn't make the function work.

As you can see in my code, I have found a workaround, but it is not ideal as it does not use the table header to identify the data values I'm looking for (see example function below for Product A).

=SUMIFS(B:B,A:A,">="&A$2,A:A,"<="&A$5)

I'm hoping for a function that can dynamically grab the values I'm looking for, based on the table header and the dates.

Thank you for any help you may provide.

Answer 1

You can also use index/match to select just the relevant column of the range (where a zero in the row parameter indicates the entire column):

=sumifs(index($B2:$D,0,match(F1,$B1:$D1,0)),$A2:$A,">="&$A2,$A2:$A,"<="&$A5)