Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










Sum If Date = Particualr Month


how to make a formula which looks at a date range and adds a specific months worth of data.

e.g. date format is 01 October 2008 so i would like to make the formula to look at all dates which include October 2008 only.


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Sum Up Month To Date
I have Excel sheet with daily sales data for differant year.
Attached is the data for 2009 as an example.

I need to compute Month to date sales in Cell F1 as highlighted.
To accomplish this i have got Public function SumDays.

This works fine but every month i have to update the start cell #, example H239 in
Sumdays(H239,c1)....for Sept i will change it to H272, and so on.
Have a look at Cell O2:P13.

i need to eliminate this so that every month correct variable is placed in SumDays(XXX,C1).

View Replies!   View Related
Date Month And Sum Formula
In the Total column, I would like to determine what the total would be as from the start date till the current date

Columns "C:I" has the dates and the Monthly applicable rates associated.

(in this example, they are annual dates, but it may be that rates change in between a year as well)

In the first set of details (Mr A), the start date is 01/10/2005

Since Mr A only begins 01/10/2005, the rates from 01/07/2004 - 30/06/2005 ($9) would not apply.

However the rates from 01/07/2005 - 30/06/2006 ($8) would be applicable for Mr A for the period 01/10/2005 - 30/06/2006 (ie.9 months) ....

View Replies!   View Related
Year To Date Sum By Month In Cell B8
I would like to see the Year To Date sum month by month in cell B8 when rolling down the cell B6. See in attached file.

View Replies!   View Related
Table Date Sum The Numbers That Belongs To The Same Month
i need a way or macro that finds in the first column the word "oleg" ...

View Replies!   View Related
Sum Year To Date Based On Month Chosen, Rank Values & Compare Rankings
1. I would like to be able to select a month from a drop down ( cell C4), and for Column B ('Cumulative Performance') to reflect the sum for each name between Jan and the month selected.

2. In Column D I would like to rank the relative position of the sum total; such that if I selected 'Dec', John would display '13' in D7, Anne '3' etc.

3. In Column E I would like to show by way of a coloured arrow (or even a smilie icon) the relative change in ranking of the sum totals evaluated for my chosen month with those calculated up until the previous month (e.g. for Anne, if I select June, the Jan to June total is 36 (rank 2 in the June total's), the May to Jan total for Anne is 32 (rank 1), therefore her relative rank movement between the June and May cumulatives moves down and cell E8 would show a red-down arrow (amber horizontal for no change and green up-arrow for an improvement in rank).

View Replies!   View Related
Macro: Check CheckBox Is True, Current Date For Day/Month, Then Sum TextBox & Cell
I am trying to allow the Command Button when clicked to go through multiple conditions before making a decision. So, when someone clicks on Command Button 3 the code should look to see if CheckBox1 is true, then it should check today's date, and if it is between a range of days, or even months, then it would add the number in TextBox1 with the amount already in cell H18. This event will happen every time someone clicks on the Command Button.

The end result is to have several sheets (4 total) for each quarter in the fiscal year, and if the dates are within those parameters, the clicking of the command button will update the correct sheet.

View Replies!   View Related
SUM By Both A Specific Month, And A Specific Year From A Single Date Field
So, this works perfectly by itself: {= SUM(IF(MONTH(I4:I17)=1,G4:G17,0))}
And this works perfectly by itself: {=SUM(IF(YEAR(I4:I17)=2007,G4:G17,0))}

But this doesn't work at all: {=SUM(IF(AND(MONTH(I4:I17)=1,YEAR(I4:I17)=2007),G4:G17,0))}

SUM by both a specific month, and a specific year from a single date field?

View Replies!   View Related
How To Lock A Particualr Cell Having Value
how to lock a particular column having the particular value for all the sheets in a single work book thru macro code?

View Replies!   View Related
Date Range Formula: Beginning Of Month To End Of Month (which Is In The Current Row)
I have log data in two columns:
Column A: Date/time (at 30 minute intervals)
Column B: Numeric data

On the last row of each month, Iím trying to perform a SumProduct on the two columns and display that result in column C.

The end of the range is determined by the month in the current row.

Iím having difficulty finding the beginning of the range, though. I need to account for both the normal dynamic calendar days & the fact that I may get data starting mid-day and mid-month.

I have this formula, but Iím not sure how to make the first array dynamic or if this is even correct approach.

Manual
=IF(OR(MONTH(A1009)=A4)*(A$4:A$65536

View Replies!   View Related
Last Ocurance Of The Last Date Used For Each Month And Then Use The Cell Number To Calculate The Column Totals For That Month
I have a spreadsheet that is now a yeare old with 5000 rows and is now going into the 2nd year

Column A is for date input and the same date can be repeated several tumes :-

1 Jan 09
1 Jan 09
1 Jan 09
1 Jan 09
2 Jan 09
2 Jan 09
3 Jan 09
3 Jan 09
3 Jan 09

Sometimes there are all 30 /31 days but normally not .

I need to find the last ocurance of the last date used for each month and then use the cell number to calculate the column totals for that month.

View Replies!   View Related
Dates - Show Month Only, And Actually Be The Month Only (not Just Format The Date)
I have a range of dates from 2003 to 2012. I formatted them to the 'Mar-01' option, but when I want to pivot on the month, Excel still reads them as the date - example 3/25/2008, 3/28/2008...and so my pivot table has multiple columns for all of the dates present in that month.

How do I truly format my dates so that excel reads them as the month only so that I can then pivot and show 12 columns (months) per year?


View Replies!   View Related
Month(Date): If The Month Is Not January It Works
I have a problem calculating something that happened last month if the month is january. At the moment, if the month is not January it works:

View Replies!   View Related
Calculate Number Of Days Between Two Date Within A Current Month Including End Date
I have two columns of dates, leave start and end dates (when people start leave i.e. annual leave). Would need to introduce column(s) to calculate how many days fell within the month including the end date and excludes weekends.

For example, if the staff on leave from 31st March to 6 April, i need to show that the number of leave taken as 1 day in March and 4 days in April.


View Replies!   View Related
Year Month Date To Month Date Year Code
Serial No Search †E220060926320061125420060612520070824620061026720061226820061127920061226 Excel tables to the web >> Excel Jeanie HTML 4

E - Year Month Date
I need F column as Month Date Year Format

View Replies!   View Related
Count Date Cells Where Date Is Previous Month
I have a spreadsheet which I use to track when a work request is recieved, when we confirm the request and when we action the request. I have been trying to write some code to count the amount of requests, receipts and actions we have processed in the last month.

My first column shows who the request is from
The second shows date recieved
The third shows date we send receipt
The fourth shows the date actioned.


View Replies!   View Related
Date Sorting With Key Field As Date And Month Only.
I need VBA code to sort the birthdays for all the employees in my company.

See the attached file for example.

I need to sort according to their month and then date and not with their year.
i.e it should not consider Year for sorting.

View Replies!   View Related
Weekday/workday Date 1 Month After The Last Contact Date
I have a list of clients that have specific requests waiting to be actioned, with a number of columns relating to client details and the status of the request across the top of the sheet. One of the columns (D) is "Last Contact Date", where I enter in the date that I last followed up with the client or made contact regarding their request. In the next column (E), I want the weekday/workday date 1 month after the Last Contact Date.

I can use "=D2+DAY(30)" to give me the date 30 days later, or "=DATE(YEAR(D2),MONTH(D2)+1,DAY(D2))" to get one month later, however what I want is the nearest WORKDAY after this date.

So, if the date is on a weekend, I need the Monday date instead.

View Replies!   View Related
Sum By Month ..
I want to use SUMIF to see if the range of cells are in the same MONTH as the criteria and sum a value in another column.

Something like this...

=SUMIF(MONTH( 'Data 2'!A:A),MONTH(Data!A2),'Data 2'!N:N)

This however throws up an error because of the MONTH tag round the first condition.

View Replies!   View Related
Date Formula: Add A Month To A Date
i use dates a LOT in my job however if i want to add a month to a date the best i came up with is the following:

BTW all dates are USA standard (m/d/y)

A1 = 12/1/2008
Lets say i want to add 3 months but the day NEEDS to be 1 because of all of my array sumif's are based off of month 12/08, 2/09 etc, and in excel 12/08 actually translates to 12/1/08. So here is the formula I have been using for the last 4 years....

=date(year(a1),month(a1)+3,day(1)

that seems a bit "Clunky" to me anyone have a better idea?

do not tell me "=a1+30" or "31" because that does not work universally.

View Replies!   View Related
Date/Month :: Specific Date Falls In?
I have a column with dates populated(examp.Fri, 15 Feb 08)in it. In the next column I need to return the month and year (month,year format) this specific date falls in?

View Replies!   View Related
IF Date Formula: IF Formula, That States If A Date Is More Than A Month After The Date In The Cell Then It Is Timely
I need an IF formula, that states if a date is more than a month after the date in the cell then it is timely and if it isn't then it is not timely. For example the date in A1 is 12/11/05, if the cell is A2 is 13/12/05 (or any date after that) then A3 should read Not Timely, if A2 is 12/12/05 (or any date before that, including a date before the date in A1) then A3 should read Timely. Is there any way to do this?? At the moment I am having to go through manually and put in either Timely or Not Timely.

View Replies!   View Related
Sum Of Income By Month
I have a spreadsheet that contains entries for each order of a product and the product amount. What I want to do is have a summary of this for income. So, if there is a date completed for the order, I want a sum of this for the month.

Order No. Order Amount £ Date Ordered Date Complete
A2 B2 C2 D2


View Replies!   View Related
Sum For Section In Month
I want when I choice the section name,The report will display only selected section sales.

I have tried the function " SUMPRODUCT", But it's display all sales regardless section name.

View Replies!   View Related
Sum By Condition For Each Month
I have a spreadsheet for to control different types of personnel expenses. For every person there are 17 rows each column. The spreadsheet itself contains a named range called "database" which in turn contains 14 columns. The second column contains the name of the expense, the first column the number of the expense (going from 1 to 17), the last twelve columns show the monthly expenses. Every month or so two or three new persons are added to the spreadsheet, and for all the others the costs are updated in the respective column.
I would like to have the monthly total for each expense type below the dynamic range. I already tried using the SUMIF- Function and it yielded good results, but unfortunately, it stops summing after the 1125 row. Reason unknown. Now I've been trying to get the DSUM()-Function working, but no luck here either. I've read the thread "Add or Sum every nth cell" and changed it slightly, so that the DSum() would compare the number of expense to a given value (1, 2, 3, etc.), and if TRUE, sum it up. Let's assume the named range starts in A2 already and the criteria are in E1:E2.

Example: Total for cost type 1 for October (3rd column)
=DBSUM(database;3;E1-E2)
Criteria:
=A2=1
The formula is acting weird and giving me the wrong sum. It doesn't change when I change the cost amount either. Ideally, I would like to have a formula that sums every cell, where the cost type =x, or every cell where the rownumber according to the named range =x (then using the MOD and ROW functions).

View Replies!   View Related
Month Wise Sum The Values
In sheet-1 I have the following table

App Value Date
A 5,2 1/3/2009
B 0,3 1/2/2009
C 5,1 1/5/2009
D 8,1 2/3/2009
E 1,6 2/13/2009
F 7,5 3/3/2009
G 6,8 3/30/2009
H 2,2 4/3/2009

In sheet-2 I have the table which has the columns as below.

Month Value
Jan-09
Feb-09
Mar-09
Apr-09

The sum of the values month wise should be calculated by replacing the comma by dot

View Replies!   View Related
Sum Daily Data By Month
I have a large set of daily rainfall and evaporation data (see attached sheet) which I would like to sum into monthly data. I have previously been doing this in Access, can anyone show me a quick way to do it in excel?

View Replies!   View Related
Sum Values Of Corresponding Dates By Month
I have a sheet that lists dates (several per month) and corresponding values. I want to sum all the dates for each month on a separate sheet. I was able to find the formula I need on another thread (http://www.excelforum.com/excel-gene...nthly-sum.html), however, my formula does not seem to work for the month of January. For January only, it actually sums all of the data available and gives me the total. Am I doing something wrong? My formula is:

=SUMPRODUCT(--(MONTH('2009 Tickets'!$B$11:$B$5000)=1),('2009 Tickets'!C$11:C$5000))

View Replies!   View Related
Sum Values With Matching Month
I am trying to Sum all the values in Column G that have the same month ( date format is mm-dd-yy) in Column B but am not sure how to do this. I have tried the Vlookup but that only returns one value.

View Replies!   View Related
Sum Based On Month Criteria
I maintain a table with projects and their respective costs / revenues.

I have a formula that automatically sets the forecast and Year-to- Date periods based on the month and date.

I need to automate the year-to-date sums such that, when the date changes and a new month acquires the YTD status, that the monthly costs/revenue of the projects are updated e.g sum of Jan-Sept in the YTD column(for this month).

A sample workbook is attached.

View Replies!   View Related
Count SUM Occurrences By Month
I've set up a trial sample register to monitor progress.

Column A contains date of receipt
Column B contains data of report
Column C contains deadline
Column D contains a formula to indicate whether the deadline was achieved, or force the cell to be blank if no date was entered,
=IF(C2="","",IF(C2>=B2,"Yes","No"))

Columns E to P contain other information.

So far ok.

I want to create a summary by month., giving the number of samples received each month, which I did by extracting the month from column A =month(A2), but i also want the number which met the deadline.

How do I count the number of Yes for each month?

View Replies!   View Related
Sum Based On Month Of Corresponding Dates
I have a worksheet of data I need to sum based on a monthly date range criteria onto a separate summary worksheet. Both are in the same workbook. I tried using SUMIF and SUMPRODUCT but can't seem to get the criteria correct when I add in LEFT into the argument for the date criteria "6/" or "06/". Here's where I'm at so far:
=SUMIF('2008'!E4:E149,(LEFT('2008'!E4:E149)="06/"),'2008'!I4:I149)

My table looks something like:

Log NoShip DateQty

000953/26/20085
000963/28/20081
000974/28/20081
000985/28/20082
000995/29/20081
001006/1/2008
001016/14/20084
001026/20/20083

The 6/1/08 entry is intentionaly blank to be filled in as the value becomes known and then the totals would of course need to be updated.

View Replies!   View Related
Sum Dated Entries By Month
If all your entries are in the form of 1/9/08 and you want to sum all the data from a specific month, how would you go about doing this? I tried using sumif, but I can't get it to work.

View Replies!   View Related
Auto Lookup And Sum Month Totals
I have a table which holds a grid of data (see attached spreasheet)

On the next sheet I have a table which I polulate with the sum of the month for each column.

I need a way to auto populate these based on the data present.

There will never be more than 365 days worth of data however this data could fall between any dates.

I need the second sheet to firstly populate itself witht the months which fall within the date range, then to sum the relevant column for each given month.

View Replies!   View Related
SUM A Range Of Sales Based On Month
I am trying to add a specific range of data
Column A include a code
Column B-X include actual data
Culumn X- AI include budget figures.
Also in cell A1i have the number of the month

For example the month is 3 (March)
I want in AK to create a SUMIF where the formula will sum columnsX+Y+Z
If month goes 4 then should calculate
X+Y+Z+AA
and so on

View Replies!   View Related
Sum By Specific 1st Day Of Month
I have 365 days of data that I need to sum by specific weekday. I need to Sum every fisrt Sundays of each month for the year, then each mondays, Tuesdays, etc. I looked all over the forum and was not able to find a thread. Is ithat possible to do?

View Replies!   View Related
Sum Numbers For Each Month By Sumif Function
Column A have random dates and column B have numbers. How can sum numbers for each month By sumif function?

View Replies!   View Related
Financial Model (formula To Equally Distribute Revenue Either Over The Next 1 Month, 2 Month Or 3 Month Period Depending On Size Of The Deal)
I m trying to write a formula for my financial model. If anyone can take a stab at a solution. I'm trying to write a formula that will equally distribute revenue either over the next 1 month, 2 month or 3 month period depending on size of the deal.

Details:
Sales will fit in 1 of 3 categories. Less than 25k; between 25k & 100k; greater than 100k.

- if under $25K, recognize in next month (month N+ 1)
- $25K-100K, recognize in two equal parts in months N + 1 and N + 2
- over $100K, recognize in three equal parts over 3 months
N + 1, N + 2, N + 3 ...

View Replies!   View Related
Dificult Sum + Sum.if Date Range Criteria
0 x
-2 x
-1 x
3 x
-4 x
1 max
5 x
8 x
9 x
1 x

I need to sum the 1st column from the bottom, to the same line of the max on the right column.
First thing i remembered =sum(xx:max(xx:xx)) but it doesn't work, i think because sum() does't allow any operations in its arguments.
Second was to store the line of the max and try to join the column and then sum them but =lin(max(xx:xx)) doesn't work either
Third i tried =sum.if() but i don't have much experience with this function.

Also i'm trying to perfect a SUM.IF with a DATE RANGE CRITERIA
instead of:
=SUM.IF(I4:I25,">=2009/1/1",R4:R25)-SUM.IF(I4:I25,">=2010/1/1",R4:R25)

why can't i use?
=sum.if(xx:xx,year(xx:xx)=2009,yx:yx)
or
=sum.if(xx:xx,year(xx:xx)="2009",yx:yx)


View Replies!   View Related
Cumulative Sum For Weekly Average Hours Over A Month
I have a column called "Weekly Working Hours" which totals the number of hours worked per week. The cell is filled in every Saturday.

In the next column I have "Average Weekly Working Hours per Month" which needs to calculate the average number of weekly hours every four weeks, filled in every Saturday.

Please see attached file. I am referring to columns J and K ....

View Replies!   View Related
Macro Or Formula That Will Sum On 1st Day Of The MOnth
after HOURS of trial and error. I was able to figure this out and get a formula written that would do what I needed. I'm trying to write a macro or formula that will sum the following

View Replies!   View Related
SUM Function (count How Many Packages Each Processor Does Per Month)
A4 is Date Assigned (MM/DD/YY) on the Activity sheet
S4 is the month assigned which I extracted from A1 (January, February, etc.)
J4 is the name of the processor

On a separate sheet I'm trying to count how many packages each processor does per month. On this sheet I've entered (as an array) =SUM(IF(Activity!$J$4:$J$390="ProcessorA",IF(Activity!$S$4:$S$390=$A$4,1,0))).
This doesn't work. However, if I delete the formula and formatting from S4 and manually type in the name of the month in each cell in S column it does work. So, how should I be formatting Column S and the the month column on the other worksheet so that the formula will work? I'm using Excel 2000 and have attached a mini sample.

View Replies!   View Related
COUNTA And Sum: Counts How Many Week Days There Are In A Particular Month
I have using the following Formula: =COUNTA(A3:A7,A10:A14,A17:A21,A24:A28,A31:A35)
Basically is counts how many week days there are in a particular month. Now I have a cell (B47) that counts how many Bank Holidays are in that month. I am having trouble using the formula then minus B47. I must be missing something really simple.

View Replies!   View Related
Search For Payments Made In A Certain Month And Return The Sum
How can I search a range of cells that have a date entered by hand for a certain month and then sum the amounts given in another range of cells. For example, search several worksheets for payments made in September and then return the sum of those payments.

View Replies!   View Related
Sum Report Based On Month & Year
I would like to calculate the sum of investments based on their expiry date and have the totals per month (and year). I have a table that looks like:

TermInterest rateMaturityBalance
24 Months7.12%11 November 200740,000.00
12 Months7.74%13 November 200750,000.00
24 Months7.05%10 January 200853,889.12
12 Months7.85%11 January 2008120,000.00
12 Months8.02%22 March 200817,000.00
36 Months6.68%30 June 200832,000.00

I'd like to have something like:

Nov 07 90,000.00
Dec 07 0.00
Jan 08 173889.12
and so on...

Admittedly I am an Excel novice, so excuse me if my question is dumb and has a simple answer (actually I hope it has :-) but I have tried to find a solution by searching forums, my books, online help, I tried my luck with sumif and SUMPRODUCT functions, even used the conditional sum wizard, but I can't get it right

View Replies!   View Related
Sum Based On Text & Month Criteria
I am trying to get the sum of some cells (integer varies in column G), but comparing one column content (exact) and dates in a different column.

I tried the following:

=SUM((Tony!G30:G44)*(Tony!D30:D44="Crazy")*(MONTH(Tony!E30:E44)=7))

Column E would contain a date, such as 01-07-07 or 1st July 2007.

In the D Column, keywords such as "Crazy" are concise and standard. However regarding dates, am I better off finding a formula that looks for cell content (Contains "july", as opposed to ="July"), or using a month function (but getting it to work)? How can I do this?

View Replies!   View Related
Sum Column Based On Other Columns Year & Month
I have the following variables in these columns

Column 1: Ship (1064, 1065, 1066 as the field contents)
Column 12: Date (21-Feb-08 as format)
Column 13: Weld Length (1000 as format)
Column 15: Defect Length (1000 as format)

What I need doing is the following is in a single cell per month add up what the total weld length is as well as the defect length as I have Jan 08, Feb 08 etc on another sheet where these values will be returned.

There is a seperate sheet for each Ship so would like a formula that I could ammend 1064 to 1065 etc

View Replies!   View Related
Using COUNTIF To Return The Sum Of Dates That Fall Within A Month/Year Range..
I want to use a COUNTIF to return the sum of all the dates that fall within a given month/year. For example: E1 Contains the date July-2009

Column A has date entries such as July 3, 2009, July 18, 2009, August 4 2009. In F1 I want to return the sum of all dates that fall within the month of E1.

View Replies!   View Related
Getting The Month Out Of The Date
I am working on a file that contains an install date. i'd like to create a new column that will only show the month per install date indicated. Can anybody help me create a macro for this?

View Replies!   View Related
Get Last Date For Month
I need a formual that will get the value that matches a month

In column A there are 52 weeks. I need to return the last non blank value for the month within the weekly period. The month value is found in column B

View Replies!   View Related
Month From Date
I use function "=month()" to the date_time column the result is "1/1/1900" or anything other than the expected result. I have attached a portion of hte sheet.

View Replies!   View Related
Copyright © 2005-08 www.BigResource.com, All rights reserved