Moving Range In SUMIF
Jul 5, 2012
At the moment I have the below formula that retrieves a value from a separate sheet (the INDIRECT part of the formula determines the sheet to be looked at, as there are numerous and they are dependent upon what is displayed in two drop down lists in cells B3 & E3).
The problem is that the ranges F6:F30000, AJ6:AJ30000 and AA6:AA30000 are not always consistently in the same column position each and every time I receive a refresh of the report. The column headings, however, are always in the same row. Obviously I don't want to have to manually update the formula every time I receive a refresh of the report, so any way that I can get the aforementioned ranges to become more dynamic, so that the correct value will be retrieved each time? I am thinking something like an INDEX/MATCH combo but I am struggling to apply that idea to the 'SUM RANGE' section of the SUMIF function. This is the formula I have so far:
=SUMIF(INDIRECT("'"&$B$3&"'!F6:F30000"),$E$3,INDIRECT("'"&$B$3&"'!AJ6:AJ30000"))
/SUMIF(INDIRECT("'"&$B$3&"'!F6:F30000"),$E$3,INDIRECT("'"&$B$3&"'!AA6:AA30000"))
View 4 Replies
ADVERTISEMENT
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
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
Jan 6, 2009
I have a simple sumif formula that says =SUMIF(W61:W112,"<30",J61:J112). In column W, there are values ranging from 0 to 5000. If the formula is written like it is above, it excludes summing values from column J when the cell in column W equals 0. Why is this? I can just add another function that says sumif "=0", but I don't think I should have to.
View 3 Replies
View Related
Apr 30, 2009
Is there a way to write VBA code that can reference a range of data that may be moved on the spreadsheet by the User? For example, if I write VBA code to reference a range of data such as:
View 2 Replies
View Related
Nov 1, 2007
I have Cloumn A with names of staff going down a2, a3, a4 etc
In Row 1 I have dates running from left to right
Within the table itself I insert a 1 when an agent is late for their shift
How do I get cloumn B to look across the top of row 1 and count 4 weeks before Todays date and use that as its range.
I'm sorry I can't post the sheet from work PC - No Colo on here
View 9 Replies
View Related
Sep 14, 2013
I'm looking to build a football data sheet on matches played with a column that adds up the last 3 match goals for the team in that row. I've only managed to get as far as adding up all goals for a team in a static date range using SUMIF. I imagine this is more complex and requires some kind of changing sum range that's relative to the cell I'm in.
I'm intermediate at best so any array formulas and VB script would preferred to be avoided if possible.
in the watered down example below, I'd like to add up all home team goals for Hereford in the last 3 matches, therefore excluding the game on the 10th (row 1) giving a total of 5. I'd like this formula to copy down from a13 to future matches and therefore update. So the next week's match will add goals for hereford from e13,e11,e8 but exclude e2, e5. Yes you've guessed it... I'm trying to work out recent team form to predict match outcomes for financial gain...
View 4 Replies
View Related
Nov 3, 2008
I have a spreadsheet where I need to perform a calculation on the lowest 10 of the 20 most recent entries. Currently I am just deleting the oldest entry and putting in the new data but I now desire to keep all my data.
How do I select the new range of 20 rows and then tell it to take the 10 lowest values to perform the calculation
View 11 Replies
View Related
Jan 17, 2010
I have a Conditional Formatting query where in essence I am needing to ‘make invisible’ a range of data by changing the font and background fill to white (and at the same time nullifying the effects of a number of other Conditional Formats). On the surface, this seems an easy thing to achieve, but the problem is, the range I need to apply this CF is a moving range and is dependant on a user changeable date.
Description of worksheet:
The sheet is managing shifts worked over 1 year by 21 people. The top 33 rows are a frozen (and collapsing) display area below which is a matrix of 365 days down and 21 people across. The TODAY button uses a macro to scan the dates and display todays entry directly beneath the freeze line of row 33. The date in M28 can be entered manually and after hitting enter, that dates data is now displayed under the freeze line. Also, using the spin button will increment or decrement the date in M28 moving the entire matrix up or down.
My query..........
What I’m hoping to achieve, is to display ONLY the data selected by the date in M28 (ie that shown under the freeze line, and for all remaining rows to be ‘whited out’, but as the user increments/decrements M28, then the “whiteout” range also needs to increment/decrement. I have whited out a second worksheet “example” as if I had selected 18 Jan (this sheet will not move as there are no buttons, but the data is still there under the whiteout).
I hope this is not TOO unclear, but I would be grateful if anyone could suggest a solution or even tell me if this is not possible – or indeed any other way of achieving the same result ( I had thought of hiding the rows below the freeze line, but the freeze/unfreeze operation would probably be too jerky.
View 9 Replies
View Related
May 18, 2006
I am not sure if my problem is in the dynamic range or not. I have attached a small sample file. I tried to create a dynamic range by creating a named range of my "LabelRow" with the following as the refers to: =OFFSET($C$32,0,1,0, COUNTA($32:$32))
When I set my cursor on this, Excel makes an outline on the whole row 32 where there is data, which gives me some clue that it understands what I am trying to define. Next, I try to pick this range up and paste it using named ranges in a macro (CopyRange). It gives me an error like it doesn't understand the named range. I do similar macro code in many other places in this project and it works with non-dynamic ranges. Here are some other details on this project. I need a dynamic range because I insert new data into the dataset, which may be 2 quarters up to 20 quarters. This insert causes a regular range to be messed up. My ultimate goal is to graph selected rows of data with the same labels, but I need to have it starting at Q1, which is pushed to the right as new rows are added.
View 2 Replies
View Related
Sep 4, 2007
I want to search a word in my Data sheet and want to know what is the column position. Example: I have a word "LastName" as a Column header in my Datasheet whose column position can be anywhere in the datasheet based on the table selected. So i want to write a generic code which give me COLUMN position for this word in the datasheet.
So if "LASTNAME" header is coming as 7th column it return me 7
if "LASTNAME" header is coming as 9th column it return me 9.
View 5 Replies
View Related
Sep 23, 2013
I have an excel work book with 6 tabs. I would like to have Excel move an entire row from one tab to another tab (removing the row and inserting it in the other tab). I.e. Example I have a tab with items that are marked as "Open Actions" so if I were to change the drop down to close. Excel would move that entire row of actions to the tab with the "closed actions" and insert into the next available row. Now if someone were to come back at a later date say no it should be reopened than I would change the drop down to open and excel would move that row back to the open actions tab into next available row. I tried a PIVOT table and no good I played with few macro and not.
View 5 Replies
View Related
Mar 30, 2007
I am using the built in moving average function to calculate the moving average of a set of numbers. There are a few things that i would like to do.
First i would like to have the last result displayed in a single cell. Then next to that cell i would like to have a cell that would specify the period of the moving average. I would like to be able to change the period in that cell and have that change it in the actual function. And finally i would like to have the moving average in a chart that would also change its period once that is changed in the respective cell. I realize that this might need some VB coding which i am currently learning.
View 14 Replies
View Related
Mar 9, 2009
I have a macro that copies and pastes into another sheet. When I add a row of data into my spreadsheet I want the macro to be able to change the range size and copy and paste the add row with the prior data.
View 2 Replies
View Related
Jan 15, 2009
Here's what I want it to look like:
Here's Macro Code I have thus far: ....
View 9 Replies
View Related
Oct 1, 2008
I had this code working on a Range in a Worksheet. I decided I needed to move the range to another worksheet. I moved the data to column AA in a new sheet called Audit_Pool and renamed the sheet name in ThisWorkbook.Sheets, but I get a runtime error at the With Range statement.
With ThisWorkbook.Sheets("Audit_Pool").Range("AA:AA")
With Range(.Cells(1, 1), .Cells(.Rows.Count).End(xlUp))
Randomize
With .Cells(Int(.Rows.Count * Rnd()) + 1, 1).Resize(1, 2)
.Copy Destination:=Sheets("Audit_Results_Data_Collection").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)
.Copy Destination:=Sheets("Audit_Results_Data_Collection").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)
.Copy Destination:=Sheets("Audit_Results_Data_Collection").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)
.Copy Destination:=Sheets("Audit_Results_Data_Collection").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)
what I missed when I moved my list to a different sheet?
View 9 Replies
View Related
Apr 28, 2008
I have the following code that works properly, Copy & Paste Same Range On Protected Sheet From Multiple Workbooks
Sub RunCodeOnAllXLSFiles()
Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook, book1R As Range
Application. ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
On Error Resume Next
Set wbCodeBook = ThisWorkbook
With Application.FileSearch
.NewSearch
'Change path to suit...............
View 2 Replies
View Related
Apr 12, 2009
I have come across this SUMIF formula which sum a range and ignoring errors produced by formulas in the range, I can't figure out how this works and need the expertise of the Excel experts here. =SUMIF(A2:A9,"<1E100"). What does "<1E100" represent and how its work
View 2 Replies
View Related
Nov 1, 2012
I am currently recorded a macros for a sumif formula. But since the number of rows will not be same every time.
I want the code for a dynamic range.
VB:
Range("E7").Select
ActiveCell.FormulaR1C1 = _
"=SUMIF('V1 Pivot'!R5C2:R32C2,'Task Level Tab'!R7C2:R30C2,'V1 Pivot'!R5C3:R32C3)"
Range("E7").Select
[Code] ....
View 9 Replies
View Related
Jan 8, 2006
Range:
C3,C14,C25,C34,C41
Criteria:
>0
sum-range:
C3,C14,C25,C34,C41
I can't get that to work since the commas in the range are throwing the function off.
View 14 Replies
View Related
May 8, 2009
I'm trying to built a range to be used at SUMIF. I have the sheet names at column A:A and want to create something dynamic to avoid selecting sheet by sheet all the neccesary ranges but sumif doesn't recognize a range like A1&"!"&B:B for example. I tried different ways but I can't get it!
View 2 Replies
View Related
May 8, 2009
How do I sumif the date falls between two dates in a quarter.
For example Date of Activity - CellBG43 = 10/10/2008
I want to sum the corresponding Cell BJ43 with cost associated with the activity if the date falls between "01/10/2008"and "31/12/2008".
I tried =sumif(BG43,">=01/10/2008 and <=31/12/2008",BJ43), =sumif(BG43,">=01/10/2008 & <=31/12/2008",BJ43) and it didn't work.
View 3 Replies
View Related
Jun 18, 2009
I have a large spreadsheet, to which I add 4 weeks worth of data, from a database printout, (cut n paste) to the bottom, this could be any number of rows. However there is a column with week numbers in for each row. I can happily use =SUMIF('FORM R60'!$H$1:$H$1248,$A1,'FORM R60'!$AH$1:$AH$1248)
This gives me the info I need on a TOTALS sheet, across various columns and rows, where the formula sits, adjusted for whichever column of data I require totals. This info is then read by another spreadsheet.
However, rather than manually change the formula (to suit a range) to add certain numbers of weeks, as I do now, I would like to include in the formula a condition say where the week numbers are >5 and <10, preferably if the condition could refer to cells.
View 4 Replies
View Related
Jan 8, 2006
range:
C3,C14,C25,C34,C41
criteria:
>0
sum-range:
C3,C14,C25,C34,C41
I can't get that to work since the commas in the range are throwing the
function off.
View 13 Replies
View Related
Jun 18, 2009
How would I SUM all the results from results 1-5 if say ITEM in column A = C?
SUMIF wont work as it only sums the first column...
ABCDEF1ITEMRESULT 1RESULT 2RESULT 3RESULT 4RESULT 52A1227723B1075554C21772 5A1010 3376A5 23357B133 7 8C7 1 29C555 10B72772
View 8 Replies
View Related
Dec 27, 2012
I have data headings in A1-E1 and actual data in A2-E2. I have a SUMIF function in F2.
A B C D E F
A B A B B
1 2 3 4 5 =SUMIF(A1:E1;"A";A2:E2)
When I insert new data columns between column E and F, the formula still refers to columns A-E although I would like to have the newly added columns in that SUMIF-function included.
For example, if I add 3 new columns, the new SUMIF-function (now in cell I2) should become =SUMIF(A1:H1;"A";A2:H2).
Do to modify the SUMIF-function to do that?
View 4 Replies
View Related
Nov 24, 2006
I have an example, see attached. I am trying to find the sum of a number of columns where the critera is between a number of days ie values is greater than 365 but less than 1788 days. The values to sum is the amount for each row where this criteria is met.
View 7 Replies
View Related
Apr 9, 2013
I have the following formula and it works.
=SUM(IF(AND((Transfers!B6="Labour"),(Transfers!C6="In")),Transfers!G6,""))
Basically the sheet 'transfers' has a column named type and one named in/out, the current formula I have does a logical test on both of these and then if they are both true takes the cost of it from a column named cost. This works fine for single rows. However on another sheet I am trying to use the above formula but there will sometimes be more than one entry for the same date on the sheet 'transfers' and all the same dates that meet the logical test's conditions need to display the sum of the cost on this other sheet.
View 1 Replies
View Related
Apr 28, 2014
I am trying to get a SUMIF formula to work where the following: - RANGE = Column N:N (These are all a list of dates) CRITERIA = Cell C20:D20 (These are the dates i want to look at) SUM_RANGE = Column P:P
Basically, I want to SUM all the numbers on Column P where the date in Column N falls between and including the dates in Cell C20 and D20
In attachement, I want to show in the YELLOW cells the SUM of Column P where the Date in Column N is between the Dates in column C and D.
View 3 Replies
View Related
Jan 8, 2014
I am working on a report for work with the following formula:
=SUMIF(A5:G8,AND(between 1-1-13 and 1-31-13,"soft cost"),G5:G8)
my formula is "IF A5:G8 IS BETWEEN JANUARY 1st 2013 AND JANUARY 31st 2013 AND ALSO IF IT IS A SOFT COST THEN GIVE ME THE SUM OF THAT ROW"
Pretty much column A contains different dates and column C indicates whether a cost is a "hard cost" or a "soft cost". If the date is within the month of January AND if it is a soft cost, I need the dollar amount in column G summed-up (must meet both criteria - January and soft cost).
View 4 Replies
View Related
