• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Referencing Columns in SUMIFS

Greetings,
I am trying to create a unified formula that can be copied without modifications to multiple tabs (this example is obviously simplified).

On the attached spreadsheet, I populated the tab labeled "North" with the shell for the desired outcome. I want to do the SUMIFS by using the division name and the product type. I know I can modify the column number for each tab and just use a simple SUMIF with Data!$C:$C being the SUMRANGE and DATA!$B:$B being the criteria range (equal to $B$1 and $C$1).

I was wondering if there is a way to do so without writing a macro. I am thinking about using the MATCH function to identify the column as =MATCH($A$1,Data!$C$1:$F$1,0), but seem to be stuck as to what formula to embed it into.

Thank you!
 

Attachments

Chihiro

Excel Ninja
In B2. Try...
=SUMIF(Data!$B$2:$B$9,B$1,INDEX(Data!$C$2:$F$9,,MATCH($A$1,Data!$C$1:$F$1,0)))

INDEX(Range,0,Column#) will return all rows of range that matches criteria.
INDEX(Range,,Column#) is equivalent.
 

Peter Bartholomew

Well-Known Member
This is restricted to Office 365 insider and monthly at present. Converting the input data to a Table to provide a sensible framework for referencing data and a further table to define the row headings on each of the Region sheets will allow the formula extracting the relevant column to be expressed in the form
= XLOOKUP( Region, Table1[#Headers], Table1 )
where Region is a sheet local reference to the cell containing the region name for the sheet (a formula could even be written to read the region from the sheet tab).

On the region worksheet this will spill as a dynamic array but it is actually a reference to a specific column within the source data table so it can be used within any formula that you could write as a result of manually selecting the field on the original table. For example, you could aggregate over product type using
= SUMIFS( XLOOKUP( Region, Table1[#Headers], Table1 ), Table1[Type], Table2[Type] )

This is all distant 'futures' for most but professionally written formulas could look very different in coming years.
 
Top