# Sumif With Offset Function?

Jul 2, 2014
I was wondering if there was a way to use sumif and offset to guide the summing column in the example below. Perhaps I need to add an additional column, then formulate a solution. I want to sum the daily returns for the TSX when the slope is positive (green), and sum the S&P when it is negative. Currently, on signal day, I am summing that day's return, which I don't want to do. I want to sum starting on the next day (blue). The B column switches back and forth from positive to negative, so I will summing different data periods going back 1000 days. I am summing the TSX returns and S&P returns seperately to make it easier. Currently I have in C1=(sumif(b2:b1000,">0",c2:c1000)) Same for D2.

Date

Slope

TSX

S&P

sumif

sumif

06/30/14

0.002774

0.34%

0.19%

[code]....

View 3 Replies
ADVERTISEMENT
May 31, 2014

I am having some difficulties using a combination of IF and the OFFSET function to display a range of cell values from another column based on a simple condition. The values I need to display at the destination cells should be offset by 8 columns to the right and "X" rows down from the reference column. The value "X" is to be determined via the IF function to check for the row index number.

For example, if Index value "X" = 8, then display the value of B2 in cell I9. IF X = "9", display B2 in cell I10 etc.

I have attached a sample worksheet that provides some examples.

View 3 Replies
View Related
Aug 28, 2009

I want to use OFFSET in a SUMIF to define the sum range argument...

Essentially this is the OFFSET (simplified) =OFFSET(K108,-50,0,100) (which when I put a SUM around, works.

But, when I put it as the sum_criteria, I get a blank result. =SUMIF(OFFSET(I108,-50,0,100),I108,OFFSET(K108,-50,0,100))

What I want it to do is look at all the cells from 50 above I108 to 50 below I108, and sum the corresponding cells in colum K.

View 9 Replies
View Related
Oct 25, 2007

I am trying to pull cell values similar to a SUMIF function (SUMIF(range,criteria,sum_range)). For example, in A1 I use a data list created from data elsewhere on the spreadsheet. In the data I created elsewhere, there are 2 columns being used. The 1st column is the information that is being used to create the list and the second column contains specific values (number or text). In the dropdown menu I select an available value (text or number) . When I have selected that value I would like cell A2 to show what the cell directly to the right of it shows from the data I have elsewhere in the spreadsheet as mentioned. I have tried the SUMIF function however it seems to exclude certain values (number or text) and I am not sure what else to use.

View 9 Replies
View Related
Sep 1, 2006

I think this is simple, but I can't seem to get what I want. I have three columns.

A: months for a few years (rows 3 - 37)

B: data-set 1 that starts in the middle of the timeline to present (rows 20-23)

C: data-set 2 that starts at the beginning of the timeline to present (rows 3-23)

Now, I need to sum for the previous 12 months at the bottom of each column. For column B, I only have 4 data points, so I want to add the 8 data points for the previous 8 months from column C. How do I do this without just doing a couple sum functions -- is there a function that will evaluate the situation on its own?

View 3 Replies
View Related
Feb 8, 2010

I am really stuck here and need help with a formula. I have attached a sheet visuals. I want to add up all the numbers that contain two specific accounts#'s. One account is directly across from the number, where I can use this formula very easily: =SUMIF(Q1:Q288,AF245,Z1:Z294)

The second account is in a different column, but it is not directly across the number. It is five cells below. So basically am having a tuff time applying these two conditions, where one condition does not directly align with the data I want to add.

View 4 Replies
View Related
Sep 11, 2009

I am trying to sum multiple columns of data by a sumif based on a criteria in column C, the columns are weekly dated, and I wish to match columns by offset this initial column with a start date and end date and sum columns in between, I have been getting close but only returns one columns values?

SUMIF(FilterCol,$C17&I$9,(StartCol:EndCol))

FilterCol is column for criteria match

Start Col is name range - OFFSET(FilterCol,0,'Summary totals line groups'!$G$4,1,1) - End col similar, (G4 is a match date to find column ref)

View 9 Replies
View Related
Nov 30, 2011

I want to sum a certain criteria and for the selected months.

Example:

Column A January February March Total

Pen 20 30 40 90

Pencil 10 10 10 30

I want to have total of Pen for the months of Jan and Feb.

View 2 Replies
View Related
Jan 29, 2010

I'm using Excel 2003. The sumif function will not allow me to ignore hidden rows in my data set that have been autofiltered. I tried using the following formula and it worked:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(CopiedB!$I$2:$I$65000,ROW(CopiedB!$I$2:$I$65000)-ROW(CopiedB!$I$2),0,1)),--(CopiedB!$A$2:$A$65000=$A22),CopiedB!$I$2:$I$65000)

The problem I am having is that the sumproduct and offset funtions used to ignore hidden rows are considered "volatile" and force a recalculation. Moreover, I have this fomula pasted 100+ times and this dramatically slows things down even when I turn autocalculate off. Is there another way to accomplish a sumif with a user defined custom function in VB? I really need to have the ability to do a sumif that ignores hidden rows and is not dramatically slow.

View 9 Replies
View Related
Apr 13, 2007

In Mr Excel's Pod Cast on April 12th, he showed how to use the OFFSET function to define a range inside a SUM function. Then he had Conditional Formatting that would highlight the range that was being summed. Can anyone tell me what the formula would be inside the Conditional Formatting dialog box to get the OFFSET range to have a certain format?

View 9 Replies
View Related
Oct 26, 2008

I am trying to use the vlookup function together with an offset function but i am not getting it to work properly.

The situation is as follows:

I have a column E in which i use the vlookup function to find its corresponding value in B which in turn refers to a named range. However, the figure i want the function to return is 2 columns to the right and 2 rows above the value which the vlookup funtion finds in the first column.

I have though of using the offset function, but i cannot figure out how to make this work.

View 8 Replies
View Related
Dec 1, 2009

In row E10:Z10 I have a series of values and in row E6:Z10 I have dates associated with each of the values in row 10.

I would like to create a formula to look in E10:Z10 for the maximum value and then return the date associated with that value. For example, if the maximum value is in P10, I would like the formula to return the date in P6.

I have tried without success to use the OFFSET function as follows:

=OFFSET(MAX(E10:Z10),-4,0)

View 3 Replies
View Related
Jan 16, 2009

I am trying to write two different codes to copy a range of data. Basically I am trying to understand how the OFFSET function works in VBA. The first code listed works fine and is as follows:

View 5 Replies
View Related
Feb 3, 2010

I would like to use the offset function as follows

Each Month a new column will be Inserted in Column V

I need to sum data starting three columns to the right of column V and then two column to the right of that plus BC15 (this will also change each time a column is inserted in Col V

I have used a sum formula before inserting a new column showing you what i am trying to achieve ...

View 9 Replies
View Related
Jul 10, 2008

I'm trying to create a formula that would return the value of the column next to it. Looking at the below example, in a new cell I would want to be able to return the value 7 based on the fact that it is in category b.

I would think an if/offset/match function would do this but I can't seem to figure it out as it may require nesting.

categoryvalue

a1

a2

a3

a4

a5

a6

b7

View 9 Replies
View Related
Apr 10, 2012

I have a total line that gets moved down with each additional newly inserted row from a vba macro. I need the total line to sum all rows in column "B" beginning with the row immediately above the total line and continuing to 30 lines above that.

View 2 Replies
View Related
Jul 5, 2007

find attached the excel sheet. In sheet1 I have named the range I have inserted a Name(Test) for range $A$1:$A$2(range contains only texts) and have a list validation on cell $B$1 using the name. This works perfectly. In sheet2 I have named the range I have inserted a Name(Test1) for range $A$1:$A$2(range contains only numerics) and have a list validation on cell $B$1 using the name. This doesn't work.

View 3 Replies
View Related
May 22, 2008

I would like a consistent OFFSET function formula across a row but my columns move from monthly to semi-annual periods at a point in time. How do I handle the move from monthly to semi-annual periods in my OFFSET formula?

View 4 Replies
View Related
Nov 25, 2012

I just want to replace the offset with find function. I have attached the sample file with the code. Just need a little change.

View 5 Replies
View Related
Mar 14, 2012

I have an Excel Workbook with data in Sheet1 and a chart in Sheet2.

I use the OFFSET function to define the data range. In Sheet2, I have 3 cells, B2(column), B4(row) and B6(period). I have slider to adjust the values in B2 and B4.

I define the data range at follow.

Xchart =OFFSET(Sheet1!A1,Sheet2!B4,0,Sheet2!B6,1)

Ychart =OFFSET(Sheet1!A1,Sheet2!B4,Sheet2!B2,Sheet2!B6,1)

When I tired to put in this into value series

=Book1.xlsx!Ychart

The error message ...

Reference not valid. Reference must be to an open Worksheet.

View 3 Replies
View Related
Apr 17, 2012

I am looking to do lookup list using the offset function. Scenario: I have 5 dealer groups, each with there own dealers. Each dealer has a list of customers. Once I select the customer it will bring up a list of their vehicles. I am stuck on selecting the customers and vehicles once the dealer group and dealer are selected.

View 1 Replies
View Related
Jul 10, 2013

I have following formula that returns #value!

=IF(OR(OFFSET(LIVE_JNL,ROW(5:5),COLUMN(C:D))="",OFFSET(LIVE_JNL,ROW(5:5),

COLUMN(C:D))=0,""),OFFSET(LIVE_JNL,ROW(5:5),COLUMN(C:D)))

Where the value in the cell which is referenced is either blank or zero then blank must be returned, otherwise the value which is being referenced by the offset formula.

View 2 Replies
View Related
Sep 2, 2008

Is there any way to use a formula rather than a direct cell reference in the Reference argument of an Offset function?

For example, rather than this:

=OFFSET(H3,0,-7,1,1)

I want to do this:

=OFFSET(max(H:H),0,-7,1,1)

Effectively replacing the starting cell of the offset function with a dynamic formula that returns the maximum value of an array.

View 9 Replies
View Related
Jun 22, 2009

I have a pivot table that has this structure:

product-id fecha entrega SUM of quantity

Q-NATA90 22-jun 15 13-jul 15 03-ago 15

Q_nata is product code (fields)

22-june, etc are the dates of the individual orders (fields)

15 is the amount of each order (data)

I tried to set an offset function in a cell outside the table that, refers to the first DATA cell (upper 15) and returns the date of the order, like this:

=DESREF(IMPORTARDATOSDINAMICOS("cantidadoriginal";$A$3;"producto_id";"G-CLA2775M";"fechaentrega";185);0;-1;1;1)

*DESREF is offset in spanish, importardatosdinamicos is IMPORTDYNAMICDATA or something like that.

But excel tells me thereīs an error in the formula and it doesnīt let me use it...I canīt find the error.

View 9 Replies
View Related
Jun 24, 2006

I want to use a dynamic range in order to use this formula below in column A as long as there is something entered in column H = SUMIF($N:$N,A5,$I:$I). any ideas on syntax and will this be using the offset function within excel. I know I have posted a couple times about getting this formula to replicate itself down certain columns as long as someone enters text in another. I looked into the offset reply information somone posted but it seemed a little over my head.

View 4 Replies
View Related
Jun 20, 2007

ive managed to come up with this formula: = OFFSET( 'G:gailMVTR2007[B052,B261A.xls]B261A45'!$B$5,COLUMN()-3,0,1,1)

which pretty much does the same job as a paste special/translate, this works fine when both files are open and updates all the values that i could ever enter, however, when i open only the file containing the link and press "update" it returns a "#value!" error, i dont understand because it works fine when the other sheet is opened and there are no values other than a single number used.

View 6 Replies
View Related
Apr 17, 2009

I am trying to used the sumIF function in refer to a particular value, e.g. Name 1.

Question 1 - What formula I can used to make sure the SUMIF function refers to that specific value, e.g Name 1?

Question 2 - Is my SUMIF function correct to calculate the SUM of hours to a Task? This is due to the data table setting? find attached:

View 3 Replies
View Related
Jul 14, 2009

I am using Excel 2003. Worksheet named: Table_Data has the following code in cell: B2

View 3 Replies
View Related
Nov 6, 2009

Sum amts based on first left character of code

code amts

102 100

200 500

101 100

results

1 200

2 500

View 9 Replies
View Related
Aug 11, 2014

I have an IF Function that I want to use to check mathematical errors between two sheets. For example Sheet2 is where I put all my material prices and Sheet1 cell A1 shows the total amount that is one Sheet2. On Sheet1, I have an IF Function in B1 that does the same calculation A1 does, but will display "ERROR" if A1 does not Match B1.

The IF Function works, my problem is that I want this to work from B1:B100 on Sheet1. But on Sheet2 the next SUM would be from row 42 to row 59.

How can I make and IF Function from B1:B100 on Sheet1 offset every 20 rows on Sheet2

For example:

Sheet1 B1 would be:

Formula:

[Code].....

Sheet1 B2 would be:

Formula:

[Code] ......

I need to do this without having to manually enter each range to sum up.

View 7 Replies
View Related