Using SUMPRODUCT referencing cells on another sheet using calculated ranges

[AFFTAXI]

I really need some help on this one. I can't figure it out.

I am trying to modify the formula below so that the cell references will calculate automatically depending on the current year. The problem is that the # of days in the 1st week changes depending on the year. This means that every year I have to manually change the cell references. I have a column named "DAY GAS" on the current sheet. The formula I have adds the values for "DAY SHIFT" Gas cost's per week. I have 52 rows of the following formulas. one for each week. Here is a 2 day example from 2016. 2017 will need a range for 7 days on the 1st row. And then the following rows ranges will have to index from there.

=SUMPRODUCT(SALES!G7:G118,(ISNUMBER(FIND("GAS",SALES!B7:B118))*ISNUMBER(FIND("DAY",SALES!C7:C118))))

=SUMPRODUCT(SALES!G119:G510,(ISNUMBER(FIND("GAS",SALES!B119:B510))*ISNUMBER(FIND("DAY",SALES!C119:C510))))

There are 56 lines between days. The sheet "SALES!" G?? contains Gas cost. "SALES!" B?? will contain a text label "GAS" and "SALES!" C?? will contain a text label "DAY SHIFT". If these conditions are present, the value in G will be summed. It seems to be mind boggling, but there are some pretty smart people on this board. It seems like someone may have had this problem before. Thank You.

 

[baitmaster]

Re: Help! Using SUMPRODUCT referencing cells on another sheet using calculated ranges

welcome to the board

whilst technically possible using INDIRECT, I wouldn't recommend it in this case. You're already using SUMPRODUCT to return values that meet certain criteria, so why not just add an extra column that contains the week number? Failing that, is there a date column we can make use of?

It sounds like your data is already in the right kind of layout, which means we shouldn't need to change the parts of it which formulas look at, they should work across the whole data set if written right

 

[AFFTAXI]

I have cell formulas like these below, which goes to a sheet of pointers which reference the correct row # for each day on the main sheet.

CellA19 =SUM(PNTRS!B2:OFFSET(PNTRS!B2,DAY(K19)-1,0))
Cell A20 =SUM(OFFSET(PNTRS!B2,DAY(K19),0,7))
Where the column "K" holds calculated week ending dates.

PNTRSB2=SUM(SALES!F7)
PNTRSB3=SUM(SALES!F63)
Etc.,Etc.

Would it be possible to use these somehow and if so, how would I use them in my SUMPRODUCT formula?

 

[AFFTAXI]

I can not edit my previous response, but the continuation of column A is:

A19=SUM(PNTRS!B2:OFFSET(PNTRS!B2,DAY(K19)-1,0))
A20=SUM(OFFSET(PNTRS!B2,DAY(K19),0,7))
A21=SUM(OFFSET(PNTRS!B2,DAY(K19)+7,0,7))
A22=SUM(OFFSET(PNTRS!B2,DAY(K19)+14,0,7))

and so on.

 

[AFFTAXI]

I guess what I really need to figure out is how to do this -> SALES!Gxx:Gxx where xx is a calculated variable # concatenated behind "G". Thank You.

 

[AFFTAXI]

I was able to use INDIRECT to solve my problem. Thank You Baitmaster.

 

[AFFTAXI]

Solution:

=SUMPRODUCT(INDIRECT("SALES!G"&L19&":G"&L20),(ISNUMBER(FIND("GAS",INDIRECT("SALES!B"&L19&":B"&L20)))*ISNUMBER(FIND("DAY",INDIRECT("SALES!C"&L19&":C"&L20)))))

Where column "L" contains row count of SALES sheet.
"L" =(K19-DATE(YEAR(K19),1,0))*56+7

WHEW!