CODEWITHSUNDEEP
excelvlookupsumifsxlookupindex-match
Ron A
Ron A

Reputation: 15

SUMIFS using dynamic column reference as the sum range

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.

enter image description here

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

Upvotes: 0

Views: 1009

Answers (1)

Mayukh Bhattacharya
Mayukh Bhattacharya

Reputation: 27233

You could try one of the followings:

enter image description here

• 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)

Upvotes: 1

Related Questions