Sumproduct with offset?

Similar Threads:

1.#REF Error in Named Range with INDIRECT, SUMPRODUCT, and OFFSET

Hi All!

I have run into an odd issue using a Named Range with INDIRECT, SUMPRODUCT, 
and OFFSET.

I have a worksheet that has a formula which will look for other worksheets 
with names that are  listed on the worksheet and once it finds the worksheet, 
the formula then looks for a Project name on that worksheet and returns the 
number in the corresponding cell.

The formula uses SUMPRODUCT, INDIRECT, OFFSET, AND IF(ISERROR).

The formula is as follows:

=IF(ISERROR(SUMPRODUCT(SUMIF(INDIRECT(Range1),$A10,OFFSET(INDIRECT(Range2),,COLUMNS($A:A)-1)))),"",SUMPRODUCT(SUMIF(INDIRECT(Range1),$A10,OFFSET(INDIRECT(Range2),,COLUMNS($A:A)-1))))

Breakdown:

Range1= "'"$A$5:$A$9&"'!$a$50:$a$60"  (A5:A9 = a list of other worksheet 
names; A50:A60 = a list of project on those worksheets)

Range2= "'"$A$5:$A$9&"'!$e$50:$e$60" (A5:A9 = a list of other worksheet 
names, as above;E50:E60 = the hours total for each project - per row -  for a 
month)

A10 is the project name on the current worksheet; I want the formula to look 
for this name on the other sheets, to return the corresponding value for the 
month in question.

OFFSET is in the formula to allow the formula to be used across muliple 
columns representing a year, and thus returning the hour total for the same 
month on the other worksheets.  The month columns are in the same columns on 
all sheets.

Sooooooooooo....when A5:A9 are all filled with names, the formula works like 
a charm (many thanks to the Excel gurus here for helping me learn about these 
complex formulae)...but when any of the cells in A5:A9 are blank, the formula 
does not return any values.  Using the Formula Auditing function and going 
through the evaluation, as I step thorugh the formula, I get #REF errors for 
the blank cells.

As soon as I put in any data, the formula works great.

Is there way to make this formula work and ignore the blank cells?  I have a 
cell range there, as some worksheets will have several worksheets listed 
(filling the range) and others will have only 1 subordinate worksheet listed. 
 I did not want to create a custom sheet each time the number of subordinate 
worksheets is different.

All help is appreciated!!!!!!!

The folks here have been great and the info I have found without even having 
to post questions has been super!

Thanks again!

-- 
Greg

2.SUMPRODUCT using offset from ROW if X marks the spot

I have a spreadsheet similar to the following:

Item,  Total,   5, 13, 1, 127, 250
Lamps, formula,   , X,   ,  X,
Desks, formula,  X, X,  ,    ,
Tables, formula,  ,  ,  X,   ,  X

The formula results look on the Item row for corresponding X in the column
and add up the numbers:

Lamps, 140 ...
Desks,  18 ...
Tables, 251 ...

I managed to get a SUMPRODUCT formula to work using specific cell ranges.
What I can't get working, is the SUMPRODUCT formula using ROW()-xxx and
COLUMN()-yyy, so that the ranges are all relative. It's OK to know that the
Item row is 2 rows above the Desks row, for example.

With several spreadsheets in this format, I can then save as .csv files,
concatenate and reload as one into another spreadsheet and see in one sheet
the results of several.

Any help greatly appreciated.

Ken

3.Sumproduct and offset

Dear list,

I've got a table that looks like this (numbers on left side are row numbers):
1	S1	S2	S3	S4	S5
2      X1	1	0	1	1	1
3      X2	0	0	0	1	1
4      X3	0	1	0	0	0
5      X4	1	1	0	0	1
For each cell in the next table (symmetric below and above diagonal) I want 
the sumproduct of two rows Xi and Xj. Cell X2-X1 should contain the 
sumproduct of row X1 and X2.

6
7	X1	X2	X3	X4	
8      X1					
9      X2	2				
10    X3	0	0			
11    X4	2	1	1		

I tried to solve this by using the following formula:
= SUMPRODUCT(OFFSET($B$2:$F$2;COLUMN()-2;0;1;6); 
OFFSET($B$2:$F$2;ROW()-8;0;1;6))
However, this results in zeros in each cell. The help file mentions that 
SUMPRODUCT considers non-numerical values as zeros.
Can someone help me with this?

Maarten

4.OFFSET and SUMPRODUCT

All:

Here is a complex function using OFFSET and SUMPRODUCT.  I belive the
OFFSET commands are finally working, leaving the SUMPRODUCT.

I have compiled this function without error.  However, when executing
the function I get #VALUE!.

Is there something wrong with the syntax for SUMPRODUCT?


Function FirstYearDepreciation(Current_Year As Double, Year_First As
Double, Fac_Depr As Integer)
    Dim FirstRange As Range
    Dim SecondRange As Range
    Dim YearDelta As Double
    Set FirstRange = Range("CI9")
    Set SecondRange = Range("DO40")
    YearDelta = Current_Year - Year_First


FirstYearDepreciation = Application.WorksheetFunction.SumProduct( _
    FirstRange.Offset(-WorksheetFunction.Min(YearDelta, Fac_Depr), 0) _
    .Resize(WorksheetFunction.Min(YearDelta + 1, Fac_Depr + 1),
1).Address, _
    SecondRange.Offset(-WorksheetFunction.Min(YearDelta, Fac_Depr), 0)
_
    .Resize(WorksheetFunction.Min(YearDelta + 1, Fac_Depr + 1),
1).Address)
End Function

5.Sumproduct Offset with an If

Hi everyone.  Is there a way to have a sumproduct embedded with an
offset function.  Essentially, I will write the formula in Column C.
I need to scan column D, and for ever instance of the word "Sales"
multiply the cell directly to the right in column E by the cell in
column A, BUT 2 rows above!  Possible?  Thanks!!

6. Using offset in sumproduct, is it possible?

7. OFFSET() not working within a SUMPRODUCT() when refering to a range in a pivot table

8. Sumproduct Indirect Named Dynamic Range using Offset