I have a column of hours spent working on a particular task, but I only want to add the hours that fall within a two week reporting period.
Is it correct to use the SUMIF formula? If so how do I write the formula to include the date range desired? Would it go under 'criteria'?
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 Relatedbased on user date ranges entered on sheet1, I'm trying to write code that will write each month of the date range on other sheets across the 2nd row. at this point I'm getting "object required" error at "Set DateStart = Cells(2, 6)"
I also want the date format to be mmm-yy (Mar 14) on the sheets even if sheet1 has a different format. I tried using sourcerange instead of DateStart, but that didnt work either.
Code:
Dim projStartDate As Date
Dim projEndDate As Date
Dim DateStart As Date[code]....
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.
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.
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).
Im Trying to use a formula of countif or sumproduct
so the formula will read the range of cells from A2:A30 to see if the word Hydro is in any of those cells, then if the date range from Cell B2:B30 is from range =>Dec-01-2007 to =<Dec-31-2007, then it will count 1.
[Code] ........
But this formula doest count.
I've tried using a multiple if count if with arrays and sumproduct. I really want it to count the ammount of time the work hydro is used during the month on december .
I am trying to use a date range as a criteria in a SUMIF function. Below are the data and formula I am using.
Forecast
6
4
15
8
Week
1/12/2014
1/19/2014
1/26/2014
2/2/2014
Formula =SUMIF(G2:J2,">L1",G1:J1)
G1:J2 - raw data shown above. L1 = 2/2/2014. Desired result = 8. Result obtained with formula above = 0 how to make this work?
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)
I am trying to do a sumif off all dates that fall into a specific year. I know I can do it by either adding a column in either of the sources to get the translated data but I was wondering if I can get this to work by it recognizing the format within the formula only.
View 4 Replies View RelatedI'm currently using the below formula to calculate the values within a certain date range.
=SUMIFS(C2:C100,B2:B100,">=2014-06-27",B2:B100,"
The attached spreadsheet has a "master" workesheet in which I enter customer info, salesperson info, and date. The totals spreadsheet automatically calculates number of sales, contact value.
I need to modify the following formulas to only calculate the data within a date range shown in 2 cells.
=COUNTIF(Master!A1:A176,PayPeriod!A4)
=SUMPRODUCT(--(Master!A1:A176=Totals!A4),(Master!K1:K176))
=E4SUMPRODUCT(--(Master!A1:A176=Totals!A4),(Master!K1:K176))
have a range of dates in a column i can match an input date with this code where the input date is in cell H20 (eg 04/04/2008)
With ActiveSheet
Range("D:D").Select
Range("D:D"). Find(What:=Range("H20"), After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
End With
the problem is that not all the dates in the range are consecutive and there may be the same date more than once eg
01/04/2008
03/04/2008
03/04/2008
05/04/2008
the problem is if the date in cell H20 (inputted by user) does not exist in column D, it all goes wrong.
what i would like to achieve is a way of incrementing the cell date by one day until it matches a date in the range, in this case if 02/04/2008 was entered 03/04/2008 would be picked up.
I have a report I am attempting to populate with data from a pivot table in another worksheet. Column A holds all the reference numbers (primary key), column B contains various start dates, and I want column C to contain all the payments made since the start date for each reference number.
The source data is a pivot table with Row = Reference number, column = transaction date, values = transaction amounts. This is an extremely large table, as I'm processing data from almost 1,200 cases, which each have around 20 payments spread over the last year, on completely random days. What I would like to do is build a formula in my report which looks up the records for the reference number from column A, and then adds up all the payments which have been made after the date in column B (and ignore any payments in the table which are before that date).
And to make things more complicated:
if an error is generated, it needs to return as 0, not #N/AThe report has the dates in UK format dd/mm/yyyy, but the pivot table has the dates in SQL format: yyyy-mm-ddThe pivot table is connected to a SQL database via ODBC and has to refresh every time it is opened.
=IF(ISERROR(VLOOKUP(A2,'Transactions'!$A$2:$B$1194,2,FALSE)),0,VLOOKUP(A2,'Transactions'!$A$2:$B$1194,2,FALSE))
i have a sheet with data in columns A-E where data gets added to daily.
In column D is a list of dates (which are in sequential order).
i have created a form which allows the user to enter a start date in one textbox
and an end date in another textbox.
how can i get this info to select the matching date range in columns A-E that could be set to print.
eg
enter start date = 18/1/2008 (entered in textbox1)
enter end date = 26/1/2008 (entered in textbox2)
print resulting range A52:E78 (Where the first matching date on the 18th and the last on the 26th).
I need some VBA code to hide columns if they are outside of a specified date range.
- the worksheet i need to run this VBA on is named 'Summary'
- Columns A to G need to remain un-hidden at all times
- from H17 to ZZ17 i have every month of the relevant years listed (all there chronologically)
- D3 holds the 'Date From' variable
- D4 holds the 'Date To' variable
I have a work book.
In column C27 and down, the user can input a date.
In column M27 down, the user chooses pass or fail.
N8, contains a date chosen by user as the "From" date and P8 the "to" date.
Cell o11 is "Passed" and cell 012 is "failed"
The user can choose a date range and input the from and to date in N8 and P8, this will count the number of pass and fails and input the number in O11 and o12.
Formulas are below.
Code:
=COUNTIFS('Aff MFR'!C27:C1663,">="&'Aff MFR'!N8,'Aff MFR'!C27:C1663,"="&'Aff MFR'!N8,'Aff MFR'!C27:C1663,"
A B
17-Feb 5.00
19-Feb 12.00
22-Feb 7.00
26-Feb 10.00
I would like to see the sum of B given it is in the range from 17-Feb to 23-Feb. My estimations so far:
=sumif(A:A,????, B:B)
How do I set up a criteria which would take values from 17-Feb to 23-Feb?
I also tried =sum(sumif(A:A,{17-Feb;?;?;23-feb}, B:B) but it wouldn't let me.
Particularly the problem is in entering the date in the array.
I have a time entry spreadsheet where I total hours worked based on the time code entered in the cell next to the hours. I use a formula such as the following: =SUMIF(F11:AS19,"=AN",E11:AR19) which checks cells F11 thru AS19 for the timecode AN and when found, takes the hours in the cell before the code and adds them to the totals way off to the right.
The problem is that when someone does a cut and paste to the time data within the range of F11:AS19 and pastes it somewhere else, like to the next employees data (no formulas are being cut and pasted, just the detail data), the formula for that new line automatically changes to exclude the cells that were pasted over. It is as if Excel thinks that you pasted over that data so it is no longer of interest to you.
It is odd because you can cut and paste smaller ranges within that range with no effect to the formula but if you include the first column or the last column of the range in your cut, then the formula gets altered. If you cut and paste the entire range, the formulas all change to #REF!
I am trying to put a SUMIF formula at the bottom of the data. Doing it manually is fine, but I want some vba to automate it, and my vba doesnt work.
Sub balance()
'adds journal line so journal balances in each month
Dim Rga As Range
Dim Rgb As Range
Dim Rgc As Range
Dim Rgd As Range
Dim Rge As Range
Dim Rgf As Range
This code just inserts text into the formula, rather than the range of cells.
I am using Excel 2007.
Sheet 1 - I have columns filled with dates (weekdays only) For example 28-May, 29-May, 30-May up to 28-Nov. Above 10 rows below each date columns and each cell has some values.
28-May29-May30-May
0.50.50.5
1.01.01.0
1.01.01.0
1.01.01.0
1.01.01.0
1.01.01.0
1.01.01.0
Sheet 2 - I have Start date defined in B1 Cell, and End date defined in B2 Cell
I need defining the formula to sum all the cell values fall between the date range defined in Sheet 2 B1 and B2.
For Example if B1=28-May B2=30-May, I want the sum of all the numbers come under the range of 28-May to 30-May, with this above example, it should be 19.5.
I have data in column A and formula in D2 is =MAX(A1:A1)
If i enter 12 in B2 cell
....... and 5 in C2 cell formula in D2 should change to =MAX(A12:A17)
If i enter 140 in B2 cell
........and 30 in C2 cell formula in D2 should change to =MAX(A140:A170)
I tried entering INDIRECT function inputting value in B2 as A12 instead only 12 but not fully succeeded
I have a cell B10 which contains a formula.
I would like a macro which copies this formula across the same row.
However, the macro should prompt the user to select a range.
Based on the range selected and in particular the columns in this selected range, I would like the macro to copy the formula only for the columns specified in the selected range.
I am trying to create a macro which gives me an input box. I need to enter 2 numbers into the input box. It would be as follows:
Input Box Msg 1 - "What is your labor cost?" (NUM1)
Input Box Msg 2 - "What is your productivity rate?" (NUM2)
Then I need the macro to take this info and enter it into a formula which changes according to the row which you are on. (I need the macro to work based on which ever cell I currently have selected). The formula would be this if I currently had a cell on row 10 selected:
=(NUM1*(NUM2*$H10))/$H10
So, if I currently had cell L10 selected, and I ran the macro and put 100 in for "NUM1" and 10 for "NUM2" and my sheet had 20 in cell H10, then the macro would enter the number 1,000 into cell L10. If I had L500 selected, then the macro would enter the final number into cell L500 based on what value H500 contained.
Let's say I have thousands of employees, but I need to determine who worked for me during a particular date range, and all I have to go on is their start date in one column and their end date in another column.
If:
A1 contains beginning date of employment
B1 contains ending date of employment
C1 contains specified beginning date (criteria)
D1 contains specified ending date (criteria)
I like to create "templates" for files that I work with on a reoccuring basis, just to make things simpler. The current template I am working on tracks items processed by day and is used for two reporting purposes; 1 totals the pay ending items processed so I have the dates laid out based on the 2 week period with formulas for that. The 2nd way it is reported is by the month. Since this is a template, only the first day of the first pay period for a calendar year needs to be entered and the rest of the dates populate based on that date. What I would now like to do is add columns for January - December monthly totals using a formula that will read something like "if column A (where the date is) contains 1/1/2014 - 1/31/2014, then sum up column L" and than copy that formula for the remaining months.
View 8 Replies View RelatedIn the expense log, Column C is a list of Dates and Column I is a list of expenses. I want to Sum the expenses in the 'Expense Log 09' to a new sheet based on a Date entered in H24 on the new sheet. I have tried the formula as shown below and Get the result #NAME?
=SUM(IF(Expense Log 'Expenses Log 09'!C8:C100,H24,'Expenses Log 09'!I8:I100)). I would Like to SUM all expenses After the posted date including that date.
I have todays date in cell A2. I have a range of dates in a row 4 and numbers below each date in row 6. What i want is to Sum the numbers below the dates in row 6 if the date in row 4 is with in 90 days before or after the date in cell A2.
Example......
The answer i should get is 4 since only 3 dates fall 90 days before or after todays date.
I have a input box that prompts a user to enter a date of a new month - it has to be the 1st of a new month. I have validation that it is a date that has been entered but then i want to validate the date entered is a month ahead of a date in a cell range on a sheet.
It is a monthly reset so it has to roll on from the previous month.
Here is what i have currently but it isn't working.
Code:
' Get user to input the first day of the new month to populate all dates with
dNewMonth = InputBox(Prompt:="Enter first Day of the new Month. Must be the 1st of the Month e.g. 01/10/2012", _
Title:="Enter Date")
' Validates the entered date is a valid date
If (IsDate(dNewMonth) = False) Then
[Code]...
I'm using nested SUBSTITUTE formula to make some changes to the some of the data. I'm also using IFERROR to return the input if it does not find the criteria I have specified. I have lot of variations in my input data.
The formula works fine for all except for date format input.
Sample:
N90232Y09--->SUBSTITUTE(A1,"N","")-->90232Y09
12335--->12335
12/3/1923---->58936 (I want the date to be retained)
why the date format is changed even if don't specify any changes for it. I expect my IFERROR to just retain the input as it is.
