I have one sheet in my document that acts as a financial summary page that pulls in data from other sheets.

Two sheets: Summary (one displaying the formula), and Sales Packages (lots of revenue data), and one column titled "Booked Revenue" within my Sales Packages sheet.

What I have right now is a cell in Summary that has a SUM of the Booked Revenue column. It's simple and works fine right now:

=SUM('Sales Packages'!J1:J) ---> $162,000

However, the 'Sales Packages' sheet updates every hour and sometimes the columns switch locations, ie. one update may have "Booked Revenue" in column J, but it may refresh and then come in under column K.

What I've done to combat this is creating this formula, which creates the value "J1" [in cell I9]:

=ADDRESS(1, MATCH("Booked Revenue", 'Sales Packages'!A1:AI1, 0), 4) ---> J1

I have another formula that creates the value "J" [in cell I10]:

=regexextract(address(1,MATCH("Booked Revenue", 'Sales Packages'!A1:AI1, 0)),"[A-Z] ") ---> J

I then have a third cell that combines all this, which provides the value 'Sales Packages'!J1:J:

=CONCATENATE("'Sales Packages'!", I9, ":", I10) ---> 'Sales Packages'!J1:J

However, when I try to wrap this new value in a SUM formula, it comes up as 0

=SUM(CONCATENATE("'Sales Packages'!", I9, ":", I10)) ---> 0

My question is, how can I get this final formula to equal the correct $162,000 instead of 0. I'm assuming it's because the CONCATENATE formula is turning into text instead of a value. Any thoughts?

CodePudding user response：

try:

=SUMPRODUCT(FILTER('Sales Packages'!A1:10000, 
                   'Sales Packages'!1:1="Booked Revenue"))