Sumif & Offset To Different Columns
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
ADVERTISEMENT
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
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
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
Aug 1, 2006
I am attempting to creating a Macro in excel with an offset feature but I am having trouble getting it to work correctly. I will include what I have created thus far, but this is what I would like it to do. There are 2 columns, A and B which are header columns for rows 2 through 75. Columns C through G contain values which are referenced in formulas in the corresponding cells in columns H through the end of the sheet. Ideally after every day I would like to run the macro to hide the next 6 columns which are showing. So on day 1 I want to run the macro to hide columns H through M. On Day 2 I would like the macro to offset and hide the columns N through S and so on. Each day an additional 6 columns (which is the all the data for the previous day) need to be hidden so that the current day's data is showing and can be easily read alongside the headings which flow vertically down columns A and B. If you could please help me with the proper form of this macro I would greatly appreciate it. This is the current macro I have made, I would like to add an offset command in there but am unsure which line to tie it to:
Sub HidePreviousDay()
'
' HidePreviousDay Macro
' Hide the Previous 6 Columns (the entire last day)
'
' Keyboard Shortcut: Ctrl+Shift+H
'
Columns("H:M").Select
Range("H2").Activate
Selection.EntireColumn.Hidden = True
End Sub
View 3 Replies
View Related
Mar 29, 2008
I have a data range set up in a step chart formation. This means there is a "Name" to the left and a time line of "values" to the right in quarterly increments (3 months at a time). Row 1 has the first month of each quarter and should be used to find the start date. Row 2 has the last month of each quarter and should be used to find the end date.
I need to find the first value from left to right and return the date directly above it in row 1.
Then....
I need to find the last value in the step chart and return the date directly above it in row 2
Example
Start Date..A...B...C....D...E...F
End Date....U...V...W...X...Y...Z
Value-1...........5...2....7........
Value-1
Start Date: B
End Date: X
Duration: = B - X
The reason i need this information is to create a Gantt Chart.
View 9 Replies
View Related
May 5, 2014
I have this from FDIBBINS working if data is on column D and E
=COUNTIF($E$2:$E$4,A2)
How would the same count function if there is a column between d and E
as attached ?
View 1 Replies
View Related
Apr 20, 2013
trying to add columns using a sumif
The names I'm using are in columns A, H and and J and the figures in B, F and K
Tried the formula belw but wouldnt work, said too many arguments
(A1:A30,E1:E30,H1:H30,J1:J30,"Dog",B1:B30,F1:F30,I1:I30,K1:K30) and tried named ranges =SUMIF(animal,"dog",age) and get value #
View 7 Replies
View Related
Mar 6, 2013
Cell C6 contains a drop down from which a month can be selected, range is Oct-12 to Sep-13
Column AB will contain a YTD sum of actuals, Column AC will contain sum of budget.
For example,
Actuals: If Mar-13 is selected in cell C6, I would like cell AB10 to sum D6+F6+H6+J6+L6+N6
Budget: If Mar-13 is slected in cell C6, I would like call AC10 to sum E6+G6+I+K6+M+O6
2nd scenario
Actuals: If Jun-13 is selected in cell C6, I would like cell AB10 to sum D6+F6+H6+J6+L6+N6+P6+R6+T6
Budget: If Jun-13 is selected in cell C6, I would like cell AC10 to sum E6+G6+I+K6+M+O6+Q6+S6+U6
I've been reading up about an offset function but can't get to grips with it - should I use that, or an index/match mix?
View 1 Replies
View Related
Mar 25, 2013
Below is a table of data I am trying to sum. In the first column, is the account code, second column has the Hotel number code, third column is the description and the 4-6 columns have amounts for March, April and May.
I am trying to sum the data for March, April and May for account 107000 using this formula, but it is only picking up the first row.
=-IFERROR(SUM(OFFSET(PnL!$C$9,MATCH($A29,INDEX(PnL!$A$10:$A$500,0),0),1,1,ControlMonth)),0) to sum
FYI:
PnL!$c$9 = is the block referred to as C9 below
$A29 contains 107000
PnL$A$10 is the block below A9 below
ControlMonth is currently set to 3
[code].....
View 5 Replies
View Related
Jun 15, 2009
I am new to excel and functions I am trying to put together a spreadsheet of computer costs. Basically I am listing various mobos, cpus, psus, etc. I have a column that signifies which mobo/cpu/etc I want to include in the actual total cost of the computer. Currently the only way I can perform the summation is to create a long drawn out calculation, which is a pain if I need to add more rows to the spreadsheet. I want to try to do a summation.
So, the grid looks something like the following:......
View 5 Replies
View Related
Oct 3, 2008
I am doing a SumIf function on some Imported data a follows "=SUMIF(FMEFactors!B:B,Long,FMEFactors!B:B)". Now, this is fine if the columns are imported in the same order but if Column B data gets moved to another Column the SumIF will give false information. I need to find the correct column to do the SumIF on.
View 3 Replies
View Related
Apr 23, 2014
I have to SUM vendor totals in my spreadsheet and SUMIF worked perfectly, but only for one column of expenses. I need to sum over fifteen columns but searching told me that SUMIF / SUMIFS will not work. Is there another way?
View 9 Replies
View Related
Jun 30, 2014
I'm trying to sumif across multiple columns, only if there is NO data in the first cell of the row. Column A is not in use - B contains some blank cells and some with data. In columns C:AM I want to sum all data in rows where the cells in Column B are blank.
Code:
=SUM(SUMIF($B$2:$B$191,"",C2:C191),(SUMIF($B$2:$B$191,"",D2:D191)),(SUMIF($B$2:$B$191,"",E2:E191)),
(SUMIF($B$2:$B$191,"",F2:F191)),(SUMIF($B$2:$B$191,"",G2:G191)),(SUMIF($B$2:$B$191,"",H2:H191)),
(SUMIF($B$2:$B$191,"",I2:I191)),(SUMIF($B$2:$B$191,"",J2:J191)),(SUMIF($B$2:$B$191,"",K2:K191)),
(SUMIF($B$2:$B$191,"",L2:L191)),(SUMIF($B$2:$B$191,"",M2:M191)),(SUMIF($B$2:$B$191,"",N2:N191)),
(SUMIF($B$2:$B$191,"",O2:O191)),(SUMIF($B$2:$B$191,"",P2:P191)),(SUMIF($B$2:$B$191,"",Q2:Q191)),
[Code] ...........
View 2 Replies
View Related
Feb 10, 2014
MR EXCEL EXAMPLE.xlsx
I'm trying to create a sumif or sumifs function that will add numbers from two columns. I've attached an example (semi populated as needed to get rid of some personal data so ignore the ref notes). On the Category Worksheet I'm trying to get column D to sum info from the March Worksheet. I would like the function in the Category Sheet to add Columns F and Columns H from the March Worksheet if they correspond to column A22 on the Category Worksheet i.e, the result of the data I've left on this example sheet should be $20....... sum of F = -130 + sum of H = 110
View 2 Replies
View Related
Nov 22, 2009
On the attached example i would like a formula in F2 which sums all the values where column equals "Smith" and column B equals "Golf European". I'm assuming it would involve the SumIf formula but i'm not sure if this is the best solution when the criteria is in two columns?
View 3 Replies
View Related
Oct 31, 2012
I have a spreadsheet with two columns, in column A i have text and in column B i have time format (01/01/1900 13:30:00) im trying to pull out information that if in column A says London to sum up the hours in column B is there analysis that would calculate this for me.
Area
Hours
Newcastle
37:30
Newcastle
37:30
[Code] ..........
View 9 Replies
View Related
Nov 5, 2007
I've noticed recently that a sumif formula will only ever add up 1 column's worth of data. Is it possible to get it to do multiple columns?
For eg, I have a spreadsheet that looks like:
Using the formula shown, I can easily add up values for 'A' codes for one of the 3 columns, but short of adding together multiple sumif's, there must be a more intelligent way of doing it?
View 9 Replies
View Related
Jul 16, 2008
Have had a search around the web forums for an answer to this and am not getting anywhere.
In column A i have text - apr, may, jun etc
In column E i have general format text - a 6 figure number
in column M i have text - eg 886.97 (a currency, but in text format)
i want to:
where column a=apr and column E=212130 sum the contents in column M
View 9 Replies
View Related
Jul 2, 2009
Is there a way to replicate this code, so I don't have to Do the With [CODE] .......
View 9 Replies
View Related
Jul 24, 2014
I have a download from an accounting general ledger which has the following:
Column A: Category Description
Column B: Country
Column C: Department
Row 1: Months
Note: Each row contains the last 12 months worth of costs
As the categories/criteria can appear multiple times, and there are thousands of lines, I have been using SUMIFS to calculate totals my required combinations (eg. Travel expense, Germany, Sales department)
Every month, the information refreshes to show the most recent 12 months worth of data. And this means I need to manually update my formulas to correct the month column headings, as everything moves by 1 month.
Would something like a SUMIF with a SUMPRODUCT work? Eg. if current month = July, then it would sum everything from the July column automatically?
View 4 Replies
View Related
Mar 17, 2009
Im trying to have a formula look at two diferent columns and if they meet the criteria add the third column.
I tried using sumproduct but It wont give me anything but error messages or a zero.
Looks like this
=SUMPRODUCT(A:A="Stewart",(G:G="Fiduciary"),C:C)
Need it to say:
if column A = Stewart and Column B = Fiduciary then add up the amount in Column C
View 9 Replies
View Related
May 7, 2013
I have 2 Columns of Holiday Dates (Column A is the "FROM" date; and Column B is the 'TO' date) and Column C is Peoples Names corresponding to the Holiday dates:
ie:
COLUMN 1 ; COLUMN 2 ; COLUMN 3
A1= 2 Jan 13 ; B1= 10 Jan 13 ; C1= Tom
A2= 4 May 13 ; B2= 10 Jun 13 ; C2= John
A3= 7 Jul 13 ; B3= 10 Jul 13 ; C3= Tom
A4= 3 Aug 13 ; B4= 25 Aug 13 ; C4= John
A5= 6 Dec 13 ; B5= 15 Dec 13 ; C5= John.
I am trying to figure out a formula (eg SUMIF) to calculate and summarize the number of Days Holiday based on a person's name (eg Tom's total Number of days Holiday days) based on the above column configuration of Dates and Names.
View 4 Replies
View Related
Jun 22, 2008
I have two sets of two columns which contain numbers as text with trailing spaces and I want to concatenate column A and B and concatenate column F and G, and then compare them, and sumif value in column I if they are the same - giving the answer in the same row as the matching data in column A and B.. but in column J.
I’ve seen sum product and haven’t been able to get it to work at all. I have been adding the trim() function in around the column references to see if that would work but am failing…
A = 2000 text
B = 1234 text
F = 2000 trailing spaces, text
G = 1234 trailing spaces, text
I = 10,000
Answer in column J
I want 20001234 = 20001234 = 10,000 for the whole column.
I don't want to change the original data as its externally sourced and used for other links etc.. This would save a few hours a week if i ( more correctly, you ) could get this to work.
View 9 Replies
View Related
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
Nov 1, 2006
- Find a value on a sheet “ORM” in Column G5:G33
-Get the corresponding value of B5:B33 of that cell
-Place that corresponding value in a canned remark: “You have the number 2 in Block, 12, 14, 25 22.
-Place this canned remark in TextBox31
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
