Sumproduct with offset?



  • 1. It worked. Thanks.
    I found the problem. Took a while, as it was really my first time tinkering with macros in any serious way. Thanks tons for the help. >-----Original Message----- >Is there anyway to create a workbook with sheet names >from a list in another workbook? Ex: I have a list of >names in one workbook. I want to create anohter workbook >in which each person has a name. >It's a pretty long list, so naming each sheet manually >would be tiresome at best. > >Thanks. > >Patrick >. >
  • 2. Listing Other Worksheet Information
    Hi! I'm creating a spreadsheet which is designed to enter updated comments against a customer account for the use by sales people - basically a poor mans CRM system! What I'd like to do is insert some sort of scrollable text screen within a worksheet that links to a raw data worksheet in the same spreadsheet. A little bit like this message box that we type in while posting a message, but pulling back information based on a specific condition like show me all the comments on customer x that have been entered previously. I'd GREATLY appreciate any advise!
  • 3. Moving multiple columns data to single column
    Hi, Ive been stuck at this thing for a while now. I found ways to do the vice versa but not this. I have text in 180 columns (40 rows in each column). I need to move all the text from B1:B40 C1:C40 ... below the text in Column A (should start at A41). So everything shows up in one column! Another scenario is that I have 180 rows of text in Column A. I need to take each cell and make 39 more copies of it in the same column. For example: Column A 123 234 456 Should show up as: Column A 123 123 123 . . . 234 234 234 . . . 456 456 456 . . . So basically each text cell is repeated 40 times before the next one shows up. Any help in these matters would be greatly appreciated! Thanks Riaz
  • 4. oops....
    Make that third sentence read: "another workbook in which each person has a sheet." That should help. >-----Original Message----- >Is there anyway to create a workbook with sheet names >from a list in another workbook? Ex: I have a list of >names in one workbook. I want to create anohter workbook >in which each person has a name. >It's a pretty long list, so naming each sheet manually >would be tiresome at best. > >Thanks. > >Patrick >. >

Sumproduct with offset?

Postby MJ » Wed, 16 Mar 2005 21:29:46 GMT

Hello everyone,

I have a large data where I need to sum certain values based on the 
criteria various rows above or below. For example, I need to sum the 
values of C one row down the criteria, A=1 and B=1 (sum of 6 and 5 in 
column C).

2	3	2
1	1	3
4	3	6
1	1	4
2	5	5

I have tried to combine sumproduct with the offset function, but I just 
can't get it to work. Thanks.


Re: Sumproduct with offset?

Postby JulieD » Wed, 16 Mar 2005 21:36:58 GMT


How about


Re: Sumproduct with offset?

Postby MJ » Thu, 17 Mar 2005 00:12:34 GMT

That simple?!
Thanks JulieD, works fine.


Re: Sumproduct with offset?

Postby JulieD » Thu, 17 Mar 2005 00:34:51 GMT

Hi Matt

just needed a touch of  "lateral" thinking :)  - glad it solved your problem


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, 

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 is as follows:



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 

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!


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.


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.

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); 
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?




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),
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

Return to MS EXCEL


Who is online

Users browsing this forum: No registered users and 60 guest