# Using SUMPRODUCT referencing cells on another sheet using calculated ranges

#### AFFTAXI

##### New Member
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.

### Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

#### baitmaster

##### Well-known Member
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

##### New Member
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

##### New Member
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

##### New Member
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.

Last edited:

#### AFFTAXI

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

#### AFFTAXI

##### New Member
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!

Last edited:

Replies
3
Views
451
Replies
0
Views
494

1,191,518
Messages
5,987,066
Members
440,074
Latest member
Emmanuelian

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back