Alternative to complex OFFSET Function

[dwestfall]

Hi, I'm currently using an OFFSET function embedded in a SUMPRODUCT formula to calculate a multiple instances of a single production stream. The formula directly underneath "Total Volume" is the following:

SUMPRODUCT($C$5:C5,N(OFFSET($D5:$D$5,ROWS($D5:$D$5)-ROW($D5:$D$5)+CELL("row",$D5:$D$5)-1,0)))

The formula works fine, however, it's repeated thousand of times across 30 other sheets and thus greatly increasing file size and slowing down the spreadsheet to a glacial pace. Does anyone have any recommendations on how to achieve the same result with an INDEX function instead of OFFSET? Thanks!

				Total
	Count	Volume	Instances	Volume
	1	40	1	40
	2	32	0	32
	3	26	1	66
	4	22	0	54
	5	20	1	86
	6	17	0	71
	7	16	1	102
	8	14	0	85
	9	13	1	115
	10	12	0	97
	11	11	1	126
	12	10	0	107
	13	10	1	136
	14	9	0	116
	15	9	1	145

<tbody>
</tbody>

 

[Oscar Cronquist]

	A​	B​	C​	D​
1​
2​
3​
4​	Count	Volume	Instances	Total Volume
5​	1​	40​	1​	0​
6​	2​	32​	0​	0​
7​	3​	26​	1​	0​
8​	4​	22​	0​	0​
9​	5​	20​	1​	0​
10​	6​	17​	0​	0​
11​	7​	16​	1​	0​
12​	8​	14​	0​	0​
13​	9​	13​	1​	0​
14​	10​	12​	0​	0​
15​	11​	11​	1​	0​
16​	12​	10​	0​	0​
17​	13​	10​	1​	0​
18​	14​	9​	0​	0​
19​	15​	9​	1​	0​

Sheet: Sheet49

Your formula returns 0 in every cell? In which cell is your formula entered?

 

[Peter_SSs]

Welcome to the MrExcel board!

As Oscar has alluded to, it is hard to know exactly where your data is located.
However, this formula appears to produce the same results and will be somewhat less burdensome of your system so perhaps you could give it a try.

Excel Workbook
	B	C	D
4	Volume	Instances	Total Volume
5	40	1	40
6	32	0	32
7	26	1	66
8	22	0	54
9	20	1	86
10	17	0	71
11	16	1	102
12	14	0	85
13	13	1	115
14	12	0	97
15	11	1	126
16	10	0	107
17	10	1	136
18	9	0	116
19	9	1	145
Volume

 

[dwestfall]

Oscar / Peter, thanks for the replies. In my spreadsheet, the aforementioned formula is located in D3, with first array in SUMPRODUCT starting in B3 and second dynamic array in C3. So, SUMPRODUCT($B$3:B3,N(OFFSET($C3:$C$3,ROWS($C3:$C$3)-ROW($C3:$C$3)+CELL("row",$C3:$C$3)-1,0))) would be the first formula in D3. Thanks in advance.

 

[dwestfall]

To clear up any confusion on what I'm seeking help with, attached is fileshare link to spreadsheet in question (apologies in advance if this frowned upon per forum rules; new to forum):

https://www.dropbox.com/s/52ip4iq3j884zbz/Array Formula Example.xlsx?dl=0

 

[Peter_SSs]

To clear up any confusion ..

Actually, for me at least, you have created more confusion.
- You say that the (first) formula is in D3, but in the sample file it appears to be in D4
- If the values in column D of the sample file are the correct results, they seem to follow a very different pattern to the results shown in post #1 above. Can you explain how the first few results (say D4 to D8) would be obtained if doing it manually?

Excel Workbook
	A	B	C	D
1				Total
2	Count	Volume	Instances	Volume
3
4	1	40
5	2	32	1	40
6	3	26	2	112
7	4	22		90
8	5	20	1	114
9	6	17	2	176
10	7	16		147
11	8	14	3	244
12	9	13	1	246
13	10	12		208
14	11	11	1	220
15	12	10	1	235
Sheet1

 

[dwestfall]

Apologies for the confusion; the pattern you posted above from my file is correct for what I'm seeking help with. Same with cell location of formula and lookup range's.

To do this manually, you'd create a two way matrix which multiples each "instance" by each volume value over a time series. I've included a second version of the file to demonstrate this visually here:

https://www.dropbox.com/s/55r6vp8mfpjrpop/Array Formula Example 2.xlsx?dl=0

 

[dwestfall]

Here's the manual form without having to open file attachment:

Book1
	H	I	J	K	L	M
1	Volume	40	32	26	22	20
2	Instances	1	2	3	4	5
3
4	1		40	32	26	22
5	2			80	64	52
6
7	1					40
8	Total Volume		40	112	90	114
Sheet1