SUMIFS using dynamic column reference as the sum range

Question

I have a table of data where I'm trying to use the SUMIFS formula. Rather than designating a specific column as the sum range, I want the formula to return the sum range based on which column has a specific header.

I've created the table below as an example. I'd want to use a SUMIFS formula where it finds the column with the header "Mouse" and uses that as the sum array, then uses Date as the criteria range (which doesn't need to be dynamic, this I can just select as fixed). I'd then have one of the dates as the criteria.

I've tried match formulas to get the Column number but don't know how to integrate this into a larger SUMIFS formula to reference the sum array. I even then converted the column number into the letters of the column, and tried to create that into the text for the formula, but couldn't get it to work.

Going crazy with this one. Thank you

Answer 1

You could try one of the followings:

---

• Using SUMIFS() with XLOOKUP() for the sum_range

=SUMIFS(XLOOKUP(F2,B1:D1,B2:D6),A2:A6,F3)

---

• Using SUMIF() with INDEX()+MATCH()/XMATCH() for the sum_range

=SUMIF(A2:A6,F3,INDEX(B2:D6,,MATCH(F2,B1:D1,0)))

---

• Or, what I see SUMPRODUCT() or SUM()^MS365 is suffice for the requirements.

=SUMPRODUCT((F2=B1:D1)*(F3=A2:A6)*B2:D6)

---