SumProduct with Range Increment..

[kundanlal]

Hello..

How to use Sumproduct with Cell Range from Data Sheet with Increment...

I have one query. Sample file with query enclosed..

Please help me in this as I need to copy every time same formula and change the range accordingly.

Thanks..

Kundanlal

[zbor]

Here, try this in C5 and extend right and down:

Please Login or Register  to view this content.

[kundanlal]

Thanks for quick reply..

I have tried with actual data base where my sumproduct output cell starts at cell AO(8), getting error..Formula used is
=SUMPRODUCT(OFFSET(('[Distributor Data-February 2016-RoMG.xls]GS 15-16'!$L$4:$L$3310),0,3*ROW(AO8)+COLUMN(AO8)-53)*('[Distributor Data-February 2016-RoMG.xls]GS 15-16'!$A$4:$A$3310=$A$1))/100

Error : #VALUE!..

Also, would you mind explaining the process flow of Offset formula in this case..

[zbor]

When start writting (any) formula you can click on it and it will show you details.

In this case OFFSET(reference, rows, cols, [height], [width])

That means from reference range ($A$4:$A$3334, from which you want to base the offset) you must move certain number of rows (0) and columns (12, 15, 18).

Now since you move from rows 5,6,7 to columns 12, 15, 18 that's formula: 3*X - 3.
But second number 3 is also dinamic because next column (D) you need to move -2 (or whole offset 3*X-2).

In you case you moving from L column (GS 15-16'!$L$4:$L$3310) by 0 rows and 3*ROW(AO8)+COLUMN(AO8)-53 (that's 12) columns, getting column X as result.

So what you doing with sumproduct is (shorter formula without sheet reference, just column):

Please Login or Register  to view this content.

If you don't have numbers in X column then that's the reason why you get #VALUE! error.

[kundanlal]

Thanks for explaining.. But again I got confused.. I will sort out this..

I am attaching actual data with some changes..

If you can make the changes there (monthly report file)and sent back the file,

I would study them and implement..

Please don't mind.

Thanks..

Kundanlal

[jason.b75]

Try this instead, it should be a little more error resistant.

Enter the formula into B8, then select B8:C26, press F2, then Ctrl Enter.

Please Login or Register  to view this content.

This will match the scheme from column A of the report to row 2 of the distributor data, and match the heading of row 7 in the report to the array, {"Our","IND","SoW"} in order to find the correct column in the distribution data.

The only time that you should see an error is when the scheme listed in column A of the report doesn't exist in row 2 of the distribution data.

[Tony Valko]

If you don't have numbers in X column then that's the reason why you get #VALUE! error.

OFFSET doesn't work on closed files.

[JohnTopley]

In B8

=SUMPRODUCT(OFFSET(('[Distributor Data-February 2016.xls]Feb 16'!$A$4:$A$10),0,MATCH($A8,'[Distributor Data-February 2016.xls]Feb 16'!$A$2:$BP$2,0)-1)*('[Distributor Data-February 2016.xls]Feb 16'!$A$4:$A$10=$A$1))/100

in C8

=SUMPRODUCT(OFFSET(('[Distributor Data-February 2016.xls]Feb 16'!$A$4:$A$10),0,MATCH($A8,'[Distributor Data-February 2016.xls]Feb 16'!$A$2:$BP$2,0))*('[Distributor Data-February 2016.xls]Feb 16'!$A$4:$A$10=$A$1))/100


Copy/drag both down


See Tony's post re OFFSET not working on closed files.

Plus response fro Jason.

[kvsrinivasamurthy]

In C5 , then drag across

Please Login or Register  to view this content.

If Start cell is not C5 make changes accordingly, ie $C$5 to be changed.
$L$4:$AL$3334 range can be extended if required.

[zbor]

Oh, yes. That too. Thank you TV.

OFFSET doesn't work on closed files.

[Tony Valko]

----------

[kundanlal]

Thanks all.. Will try solution provided..

[kundanlal]

I have used following formula with actual database and values..

=SUMPRODUCT((INDEX('[Distributor Data-February 2016-RoMG.xls]GS Feb 16'!$L$4:$AL$3334,,1+((ROW()-ROW($C$5))*3)+COLUMN()-COLUMN($C$5)))*('[Distributor Data-February 2016-RoMG.xls]GS Feb 16'!$A$4:$A$3334=$A$1))/100

Works fine up to Certain increment, i.e. upto 28, and gives error #REF.. I have also checked actual database, there is no unusual data..what can be possible problem..

[kvsrinivasamurthy]

In the formula change this range
$L$4:$AL$3334
as
$L$4:$ZL$3334
Works upto 230