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


Advertisements:










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 Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Average Daily Data By Month
I have a workbook with two sheets - DATA and SUMMARY.
DATA has two columns - date and data_value. Data will be added to this sheet on a regular basis

SUMMARY has two columns - month and average

In the column for average I would like a formula to calculate the average of data_value for each month without having to manually determine the range for the particular month.

View Replies!   View Related
Sumproduct Multiple Daily Transactions By Date And Month
Can someone tell me what I'm doing wrong for the weekly sums in this spreadsheet? The monthly sums work fine.

PS I can't use pivot tables. This spreadsheet is a quite small part of a more expansive set of worksheets, from which I am pulling data.

View Replies!   View Related
SUM Of Daily Inventory
I AM TRYING TO SUM OF EACH DAILY INVENTORY ITEM. PREVIOUSLY I USED FORMULA SUGGESTED FROM TEETHLESSMAMA (=SUMPRODUCT(--($A$5:$J$13=A19),$B$5:$K$13)).

BUT THIS FORMULA NOT WORK FOR NEW FORMAT OF INVENTROY DATA. I tried to make some change in it to get the result, which is not working well.

View Replies!   View Related
Store Data Daily
I have a two rows of data one containing names and the other containing corresponding numbers. The names are static and the numbers change on a daily basis. I want to be able to copy the numbers to a static table next to each name on a daily basis (so I can see what the value was a few weeks ago).

Is there anything I can write to do this job?

My thinking was to set a vlookup to grab the data but i'm not sure how this would work because the vlookup would change daily when the numbers change

View Replies!   View Related
Looping Through Daily One Minute Data
I'm trying to create a macro to loop through daily one minute data.I believe the flowchart would be something like:

Create Variable

For each day in recordset

Loop through each minute record

Run system rules

Copy to Seperate worksheet

End

Additional Info:

Data is in columns B-G (Date,Time,Open,High,Low, Close)

Sample system could be something like:

If Current record close price is > Past 2 records Close Price Then
Buy 100 Shares
Liquidate if poistion moves against by 10%
Take Profit if position increases by 5%
Else close by days end

View Replies!   View Related
Finding The Last Value In A Week From Daily Data
Column A = Date
Column B = Numbers
Column C = results

As you can see Column C, I manually did what I want the formula to do, which is get last number from the previous week. (weekdays only, but can be either 1,2,3,4 or 5 days due to holidays).

View Replies!   View Related
Move Data Daily Into New Column
I have used the "Import External Data-Web Query" to gather financial data.

This data is updated daily by the web site. The data fills up columns A2 to E 6000.

The data on Column B is of importance and I need it to be stored daily. I need a code that will store todays Column B data in column F, tomorrows Column B data in Column G, dayafter's column B data in Column H and so on..

In short, I need to create a database automatically..

View Replies!   View Related
Finding Highest Value In A Week From Daily Data
I have two columns.

A column = contains dates but does not always have 5 days in a week. Holidays are not entered.

B column = price data for each day

All I want to do is get the highest price from the previous week. So for example last week highest price was 5000 then column C will display 5000 for this entire week. I tried using WEEKNUM and WEEKDAY but i am clueless on what to do after that. I'm trying to avoid macros or VB since im not that advance with that. But if I have to I will.

View Replies!   View Related
Summarize Daily Data Into Weekly Average
I have two time series which span several years. The first series measures stock levels on every Friday (52 values a year). The second series measures the price level every weekday (260 values a year).

I'd like to condense the daily data in to a weekly average, can I do this easily? For example, I could manually use the Weeknum function to calculate the week number of each daily price data, then find the average daily price for each week, thus giving me 52 values which I can compare to the weekly stock series. Is there an automatic, fast way of doing this? Alternatively, I'd be happy to settle with a monthly average. Is this possible via macro's or does VBA need to be used?

View Replies!   View Related
Calculating Quarterly Averages From Daily Data
I have a sheet with daily data starting from 01/01/2000. I want to calculate daily averages for each quarter (i.e 2000Q1 value will be the average of values between 01/01/2000-31/03/2000, 2000Q2 will be average of values between 01/04/2000-31/06/2000, 2000Q3 average(01/07/2000-31/09/2000) and 2000Q4 will be the average of (01/10/2000-31/12/2000) etc. for all years afterwards.

I want to have the values in the corresponding cells starting with range ("e2")

View Replies!   View Related
Automatically Create Monthly Totals From Daily Data
In column A I have dates, in column B I have data.

What I am trying to do is get the monthly totals from the data so instead of:

01/02/08 - 52
06/02/08 - 87
14/03/08 - 23
12/13/08 - 12

I would get:

Feb 08 - 129
Mar 08 - 33

View Replies!   View Related
Calculate Monthly Standard Deviation From Daily Data
I've daily data of a stock indices returns and I would like to calculate the monthly standard deviation. Currently, I'm using the following worksheet functions: =STDEVP(C2:C20)*SQRT(COUNT(C2:C20))

However, the range changes from month to month, which makes the process of calculating the monthly standard deviation to be quite tedious if I've about 10 years worth of data. I assume I could somehow substitute the range with a dynamic range, but I'm struggling to come up with the correct formulation that would do that.

View Replies!   View Related
Formula That Compares Month Over Month Data
I am trying to create a formula that compares month over month data. If the prior month is 0 I get an error. I am having trouble with incorporating ISERR into the formula to eliminate the error.

=IF((C26-B26)/B26

View Replies!   View Related
Month To Month Analysis On Monthly Data
In cell A2 on Sheet 1 = January. On sheet 2 in cell A2 I need it to = February, On sheet 3 in cell A2 I need it to = March, On sheet 4 in cell A2 I need it to = April, etc.... How can I do this with a regular text formula, not VBA coding.

View Replies!   View Related
Pivot Table Report Daily Data & Group Same Days In Year
Last week I posted a question related to formatting a cell to return a Day of the Week versus a numerical representation IE "Wed" instead of 02/20/2008 12:00AM. The solution provided worked for me:

1) Format cell to DDD MM/DD/YYYY HR:MN. Cell range (A1:A500)
2) Format destination cell with DDD. Cell range (B1:B500)
3) Destination cell (B1) = to original cell A1
4) B1 displayed data as "Wed"

However, the issue I still have is; I wanted to create a pivot table summarizing a year activity by Day of Week (in other words 7 entries for the year) and the pivot table still recognized all the MM/DD/YYYY. I ended up with a table displaying every day of the year instead of a yearly summary by Day of Week. Is there some way to strip out all the other numerical data from the new column I created to run a pivot table by Day of the Week for a whole years activity?

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
Tracking Daily Total Sales And Individual Tender With Data Extracted From .dbf File.
I want to track daily sales of a shop with the tenders (Cash, Master, Visa)seperated.

Everyday there will be a file ctp.dbf from a folder YYYYMMDD (previous day date) which contains sales details.

I tried to use sumif commands and everything is working fine. everytime i have to open book.xls and from it I do a files>Open to open the ctp.dbf for the calculation to be done. is there a way where by i can open 1 file and everthing i calculated properly?

Also this book.xls can only do for 1 day how can i go about having the daily sales detail of the month (look something like sales summary.xls) or even year in 1 excel file?

attached is book.xls and sales summary.xls for reference.

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 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
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
Lookup Invoice Numbers From A Raw Data File With ~5,000 Line Items On A Daily Basis
I have a spreadsheet, in which I need to lookup invoice numbers from a raw data file with ~5,000 line items on a daily basis. The lookup is based on two criteria searches (1) search product type (2) search product make. In this example, I have 4 product types:

1 car
2 truck
3 boat
4 motorcycle

For this example I want to search invoices; (1) first search for cars only (2) search for product make. In my attached example, the first item (cell E2) would return invoice number 7147875-FRD from the raw data file. The second item (cell E3) would return invoice number 7147877-NSN.

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
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 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 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
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
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
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
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
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
Sum The Sales Revenue Column Of The Attached Per Month Into The Yellow Highlighted Cell @ The Bottom
is there a formula i can use to sum the sales revinue column of the attached per month into the yellow highlighted cell @ the bottom?

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
Copyright 2005-08 www.BigResource.com, All rights reserved