Calculating totals by month from whole date

I'm trying to figure out the proper calculation to pull totals by dat
in Excel. Here's my problem:

I have a workbook with 2 worksheets; one is the main data, the other i
the statistics from the main data. Within the main data, there is 
column titled "date paid" and another column titled "total paid" (ther
are a bunch more columns, but they don't matter for this problem). Th
"date paid" column will contain dates such as 1/1/04, 5/15/04 etc. O
the stats page, I have columns titled for each month of the year an
would like each to include how much was paid for each month from th
main data worksheet.

For example:
On the main data page I have
date paid total paid
1/1/04      $500
1/15/04    $200
2/3/04      $100
3/30/04    $300

Here's how my stats page SHOULD look, but I can't make it:
January      February      March
$700          $100            $300

How can I get the totals to come over by month?

Any help would be GREATLY appreciated

--
Message posted from http://www.ExcelForum.com

0
8/1/2004 7:27:39 PM
excel 39879 articles. 2 followers. Follow

3 Replies
390 Views

Similar Articles

[PageSpeed] 32

Hi
one way:
use a pivot table and group by months. See:
http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html
http://www.ozgrid.com/Excel/excel-pivot-tables.htm

Another way would be to use SUMPRODUCT. If you second sheet contains in
row 1 the month name as 'Text' and you have only data for one year
enter the following in A2 (for January):
=SUMPRODUCT(--(TEXT('data'!$A$1:$A$1000,"MMMM")=A$1),'data'!$B$1:$B$100
0)
and copy to the right


--
Regards
Frank Kabel
Frankfurt, Germany


> I'm trying to figure out the proper calculation to pull totals by
date
> in Excel. Here's my problem:
>
> I have a workbook with 2 worksheets; one is the main data, the other
> is the statistics from the main data. Within the main data, there is
a
> column titled "date paid" and another column titled "total paid"
> (there are a bunch more columns, but they don't matter for this
> problem). The "date paid" column will contain dates such as 1/1/04,
> 5/15/04 etc. On the stats page, I have columns titled for each month
> of the year and would like each to include how much was paid for each
> month from the main data worksheet.
>
> For example:
> On the main data page I have
> date paid total paid
> 1/1/04      $500
> 1/15/04    $200
> 2/3/04      $100
> 3/30/04    $300
>
> Here's how my stats page SHOULD look, but I can't make it:
> January      February      March
> $700          $100            $300
>
> How can I get the totals to come over by month?
>
> Any help would be GREATLY appreciated!
>
>
> ---
> Message posted from http://www.ExcelForum.com/

0
frank.kabel (11126)
8/1/2004 7:58:46 PM
Thank you so much for the advise Frank! I chose to use the SUMPRODUC
formula you posted instead of the pivot table since pivot tables don'
update automatically, and I'm not the only one that will be using thi
spreadsheet.

The SUMPRODUCT worked perfectly! I don't know how, but it worked an
that's what matters. I'll delve deeper to find out how it works.

I wish I would have posted my problem yesterday... then I could hav
spent the day playing instead of working!

Thanks again!
Marci

--
Message posted from http://www.ExcelForum.com

0
8/1/2004 10:43:19 PM
Hi Marcie
as explanantion see:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--
Regards
Frank Kabel
Frankfurt, Germany


> Thank you so much for the advise Frank! I chose to use the SUMPRODUCT
> formula you posted instead of the pivot table since pivot tables
don't
> update automatically, and I'm not the only one that will be using
this
> spreadsheet.
>
> The SUMPRODUCT worked perfectly! I don't know how, but it worked and
> that's what matters. I'll delve deeper to find out how it works.
>
> I wish I would have posted my problem yesterday... then I could have
> spent the day playing instead of working!
>
> Thanks again!
> Marcie
>
>
> ---
> Message posted from http://www.ExcelForum.com/

0
frank.kabel (11126)
8/1/2004 10:54:33 PM
Reply:

Similar Artilces:

Date format issue when submitting from a userform to a spreadsheet
Hi, I have a userform that I've generated which routes dates onto a spreadsheet based on the users input. I am having a bit of a frustrating time with the dates, it would appear that in the process of moving the date from the userform to the spreadsheet some dates are switched/transposed. I'll give an example. If someone enters 09/02/2004 on the userform excel seems to look at 09 and assume it is a month and transposes the dates to 02/09/2004(this does not appear to be a US/UK format issues as I have already gone down that road). However, if the date 13/09/2004 is entered i...

Using iterations to calculate circular references.
I am working with data that requires using circular references in some calculations. I checked the "iterations" box to allow for these calculations to work, and Ecxel seems to be calculating everything correctly. The problem is that after I close the workbook, when I later reopen it to continue working, all the cells that are a part of the circular reference display the #VALUE! error. I can fix this by deleting one cell that is part of the loop and then undoing that command, or by copying the formula from a cell, deleting it, and pasting it back into the same cell. I have to d...

varible calculation
I am trying to create a calculation for haulage/trucking charges 1-300 kilos £ 30 300-400 kilos £ 35 400-500 kilos £ 40 What formula could I use to accomplish this?? -- kinsey assuming that you mean: 1-299 300-399 400- then try: =LOOKUP(A1,{0,300,400},{30,35,40}) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk "kinsey" <kinsey@discussions.microsoft.com> wrote in message news:46C0E5C2-11A6-4AF7-9F2A-8D4D23F4498B@microsoft.com... &...

calculating return in a range
Hi, I have a range of stock prices. with indication for buy or sell. and i want to calculate the return of the range for example : buy 522.73 buy 527.9 buy 528.81 buy 532.33 buy 535.1 buy 536.23 buy 537.03 buy 538.57 sell 537.54 sell 532.86 sell 538.14 sell 533.08 buy 531.22 I want to calculate the return for the first buy signal. the range is from the start to the first sell signal 537.54- 522.73 then the sell range untill the first buy signal 531.22- 537.54 and so on.... -- kman ------------------------------------------------------------------------ kman's Profile: http://www.e...

SUMIF function not calculating from closed worksheet
We recently upgraded from Microsoft Office 2000 to 2007. Prior to the upgrade each department would access a spreadsheet that would load data from a common lookup spreadsheet The loading process was performed as follows: The lookup file and each department file would be opened to update the link for that month and then saved and closed. When the department head would open the department file, a question would appear asking if they wanted to update the link, if they answered no the spreadsheet would open and display the calculated data. If they answered yes, the file would o...

Keyboard shortcut for current date and time
Hi, Ctrl+ ; inserts current date and Ctrl+Shift+; inserts current ti me Ctrl+Shift+; inserts the current time with the date serial as 0 and not the current date's date serial. Presently I am adding the two (ie current date and time) to get the current date and time. Is there a keyboard shortcut that does this? Thanks in advance. Regards, Raj CTRL+; then SPACE then CTRL+SHIFT+; -or- =Now() -- Jim Cone Portland, Oregon USA http://www.mediafire.com/PrimitiveSoftware "Raj" <rspai9@gmail.com> wrote in message Hi, Ctrl+ ; inserts current dat...

Compare Now() to a European date
This is driving me nuts, I have a list of certificates. In column B their expiry dates are entered as Europeans, some at least, do. Like today would be 20080524. I want these cells to change colour with conditional formating. For instance becoming yellow when there is less than three months between now and the expiry date, and then becoming red when there is less than one month to expiry. Else they should remain without colour. I have read through a hundred posts dealing with similar needs and seemingly fine replies, but I get nowhere with my particular sheet. When I format my B cell as ...

Formula for date field
1.I have simple dates in one column (say column A) . 2.In the next column(Column B) I would like the date five months after Column A to be displayed.Eg if Column A has an entry of 9th June 2007,Column B should display 8th November,2007. 3.A simple formula does not do the job as this does not take into account the different number of days in different months! regards S.Sanatani Your post is a bit ambiguous since you don't really say how the different number of days in months should be handled. One way: A1: <date> B1: =DATE(YEAR(A1),MONTH(A1)+5,DAY(A1)-1) In articl...

Date Format turn to Year
Hi, I tried to convert the date to YEAR and then the year plus 25 Years later. =Year(A1) I'm getting the result 1900 instead of 1965. I tried to add 25 years later to 1990 from 1965. Your help would be much apprecated. Thanks What's in A1? Are you sure it's a real date? "learning_codes@hotmail.com" wrote: > > Hi, > > I tried to convert the date to YEAR and then the year plus 25 Years > later. > > =Year(A1) I'm getting the result 1900 instead of 1965. > > I tried to add 25 years later to 1990 from 1965. > > Your ...

using dates Part 2
Karl was great in helping me get to this point with dates, now I'm wondering if we can take it 1 step further? For Activity Dates prior to 2/1/2007 they are using a normal reporting year and the formulas below take care of Activity dates >2/1/2007? So for example prior to 2/1/2007 1/1/2006 would have a B_Qtr of 2006-1 1) B_Qtr - 2011-1 --- Format(DateAdd("m",11,[ActivityDate]), "yyyy - q") 2) Year - 2011 ---- Year(DateAdd("m",11,[ActivityDate])) 3) Qtr - Q1 ---- Format(DateAdd("m",11,[ActivityDate]), "q") -- Than...

Today's date on an Active X Calendar
Could anyone tell me how to set the properties so that the ActiveX calendar I have in the database, displays the current date when the program is openend. I thought this would have been easy, but obviously not! Thanks for any help. CEL504 wrote: > Could anyone tell me how to set the properties so that the ActiveX calendar I > have in the database, displays the current date when the program is openend. > > I thought this would have been easy, but obviously not! > > Thanks for any help. http://groups.google.com/group/comp.databases.ms-access/msg/1564d683ede98f8c Jame...

How to display Total Item Quantity on receipt and Status bar
I would like to print a total Quantity of items purchased on receipt and the status bar. I found a way of displaying them but the problem is that we use tag along items for alcohol tax . This tax differs based on alcohol content and size, example a 6 pk of beer has $0.20 tax plus sales tax, a 750 Ml a bottle of wine has $0.116 tax . If i ring up a 6pk or beer this would show up as 2 lines(line one name and price for beer , line two alcohol tax tag along item) on POS screen and the Total items and total QTY count shows as 2 when in reality only 1 item was sold. This gets even more conf...

Getting right date value
I setup my DTPicker control to be used only as a date control, yet I'm noticing that sometimes it will give back a date AND a time all in the same "value" variable. Since it appears that a variable of type "Date" can give back both a date and time, how can I eliminate the time half of a date value??? I might not be able to exactly control the DTPicker control to give me JUST a date, so I'm just curious what to do if it gives me back both a date & time. thank u Hi, Try this : Dim x as date x = cdate(clng(DTPicker1.value)) &qu...

I need a formula to sum column b if column a is between two dates
I have an excel spreadsheet with employees time off. I need a formula that will add column b if column a is betwee two dates. For example: if column a is between 9/22/04 and 9/21/05 then add column b. I have tried all different formluas but can't get this to work. ...

Making a template that puts the current date in the document so that does NOT change
I'd like to make a template that sets up some standard headers and formatting for new Word documents for a night school course I'm enrolled in. Among other requirements for all papers is to put the date the paper was created at the top. I'd furthermore like it so that if I need to reopen the document after creating it to say print another copy, the date written at the top will not change. In other words, when I create a new document using the template the current date is put near the top, but when I subsequently open the file for editing or reprinting it does NOT automat...

Show all months on report
I have a report that shows total shipments by month for specific items by totaling in the Month Footer. Some items have no shipment on some months so that month is not listed. Is there a way to have the report show all months regardless if there were shipments that month or not? Create a table name CountNumber with field CountNUM having integers from 0 (zero) through you maximum spread. Build the query below. SELECT DateAdd("m",[CountNUM],CVDate([Enter start date])) AS [My Dates] FROM CountNumber WHERE (((DateAdd("m",[CountNUM],CVDate([Enter start date])))<=CVDa...

Propose a meeting with multiple dates in Outlook
How do you propose a meeting with multiple dates? Recurrence button S wrote: > How do you propose a meeting with multiple dates? > "S" <S@discussions.microsoft.com> wrote in message news:1448A718-D76A-4874-9FC0-03AFD4367566@microsoft.com... > How do you propose a meeting with multiple dates? See this: http://www.slipstick.com/calendar/pickmeeting.htm -- Brian Tillman [MVP-Outlook] On 3/11/2010 7:16 AM, S wrote: > How do you propose a meeting with multiple dates? > A third-party solution called Tungle works nicely for this. ht...

how do I not show a record in a report when the total is zero?
I have a very simple DB where I track items. I have an ID, Description, Intake #, Sold #, Remaining # (this is from subtracting the sold from the intake), and a total inventory (this is calculated using Dsum on remaining inventory based on description matches). I have a report that I wish will show all records that have a remaining balance, and just exclude the ones that have a zero balance. I tried: Private Sub Report_Load() If Remaining.Value <= 0 Then ID.Visible = False Description.Visible = False Intake.Visible = False Sold.Visible = False Rema...

Null date parameter
Hi How can I pass a null value to a date parameter in a Sub? Thanks Regards hi John, John wrote: > How can I pass a null value to a date parameter in a Sub? you have to pass it as Varian as normal data types cannot be Null: Public Sub yourSub(ADateNullable As Variant) On Local Error GoTo LocalError Dim DateValue As Date If IsNull(ADateNullable) Then Else DateValue = CDate(ADateNullable) End IF Exit Sub LocalError: If Err.Number = 13 Then ' Type mismatch. End If End Sub mfG --> stefan <-- ...

Serial Date in Multiple Worksheet
Hi, I need to create a workbook with the same Multiple Worksheet (TAB) form on it. This form will be used for each day of the month. The problem is I want to be able to enter the date on the first TAB and has the date replica to each worksheet add 1 day to each sheet. Example: The first sheet - Monday, December 26, 2005; the second - Tuesday, December 27, 2005; the third - Wednesday, December 28, 2005; and so on. Thanks in advanced. Try running the sub below (Sub is by Dave Peterson, and was plucked from a post by Gord Dibben in .newusers) Here's how to set it up .. In a new b...

Date & Time
Hi, I have a form called frmDetails and I have set a text box (txtDate) in the form with the control source of =Format(Now(),"dddd"", ""mmm d yyyy"", ""hh:nn:ss ampm") but the problem is I want this Time & Date to be recorded in the table (called tblDetails) as it needs to be used for referancing when entries were entered into the database. Can anyone tell me how to do this. Thanks Fiona Control source should be the table column. Make the default value your Now() etc. -Dorian "fiona.innes@metacor.co.uk" wrote: > Hi, &g...

Transaction dates a day early
Hello, Using M06 with direct connect & bill pay to my bank, the downloaded transaction dates entered are a day early in Money. If I view my account through my bank's web interface, the date would show as I'd expect (e.g. 11/21) for a given transaction. But when viewing my account in M06 (again directly connected/downloaded from bank), that same transaction has a date of 11/20. All transactions from this bank seem to "post" a day early in Money. I do let Money change the transaction date from what I entered to what the bank says (that's the way I want it). I a...

input date
hi all, is this possible: i will type 011005 and then excel will automatically format it as 01/10/05 and will be treated as date? thanks. Rufino Only with VBA code or a function in another cell, there is no way with the user interface of doing this automatically -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS "rufino palacol jr" <rufinojr54@gawab.com> wrote in message news:%23Ve5wjg%23EHA.3376@TK2MSFTNGP12.phx.gbl... > hi all, > > is this possible: i will type 011005 and then excel will automatically > fo...

Date Elimination
I have a worksheet with mainly dates in column A in the format of '25 Aug 2008'. Is it possible with a macro or similar to delete lines beyond a certain date (2 years hence)? Basically, I'm not interested in data more than 2 years old. This would eliminate a lot of data and make for a more viewable worksheet. try this Sub del_date() ActiveCell.Range("A1").Select Do Until ActiveCell.Value =3D "" dt =3D Date - 730 If ActiveCell.Value <=3D dt Then ActiveCell.ClearContents ActiveCell.Offset(1, 0).Select Else ActiveCell.Offset(1, 0).Select End If Loop End ...

Incrementing the date
I print out a register, 1 for each day of the month is there anyway of incrementing the date for each one, so i don't have to change the date each time i print it out. Are you wanting the date you printed it? mills08 wrote: > I print out a register, 1 for each day of the month is there anyway of > incrementing the date for each one, so i don't have to change the date each > time i print it out. And do you put the date in a cell (what cell)? Or do you put it in the header or footer? I'm guessing that you want to preprint the forms for a month (like on the first o...