 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
Mac Forum / Applications / Excel / May 2006subtotal by a range of dates
OK: I know there must be an esier way to do this, i'm working with a data base in wich i have a column of dates and next to it a column with quantities i need to subtotal the quantities in a monthly basis example: all may dates and total by quantity. 5/19/2006 10400 275,000 5/30/2006 18000 189,906 6/2/2006 3200 275,000 6/2/2006 22000 189,906 6/12/2006 22000 189,906 6/16/2006 12800 275,000 want to separete may from june and sum quantities from each month. anybody??? let me know if you need more info. thx On 5/24/06 12:56 PM, in article A0BFE29B-D03C-436E-B2A0-5C64E4A65D0C@microsoft.com, "rosa" > 5/19/2006 10400 275,000 > 5/30/2006 18000 189,906 > 6/2/2006 3200 275,000 > 6/2/2006 22000 189,906 > 6/12/2006 22000 189,906 > 6/16/2006 12800 275,000 I suggest you add another column containing the month. Then use Subtotal from the data menu.  SignatureBob Greenblatt [MVP], Macintosh bobgreenblattATmsnDOTcom Assuming that A2:C7 contains the data, let E2 and E3 contain 5/1/2006 and 6/1/2006, then enter the following formula in F2 and copy down: =SUMPRODUCT(--($A$2:$A$7-DAY($A$2:$A$7)+1=E2),$B$2:$B$7) Hope this helps! > OK: I know there must be an esier way to do this, i'm working with a data > base in wich i have a column of dates and next to it a column with quantities [quoted text clipped - 12 lines] > > thx Dominic: i think what you're saying it's what i'm looking for, is there a way i can send you my database by e mail and maybe you can see the whole database???? if you are up to it? or i can call you???? let me know > Assuming that A2:C7 contains the data, let E2 and E3 contain 5/1/2006 > and 6/1/2006, then enter the following formula in F2 and copy down: [quoted text clipped - 19 lines] > > > > thx Have you tried my solution? > Dominic: > i think what you're saying it's what i'm looking for, is there a way i can > send you my database by e mail and maybe you can see the whole database???? > if you are up to it? or i can call you???? let me know yes, but i'm not so good with formulas, i don't know what i'm doing wrong, my database has other columns and information. it's not coming out right i have a lot of diff dates. > Have you tried my solution? > > > Dominic: > > i think what you're saying it's what i'm looking for, is there a way i can > > send you my database by e mail and maybe you can see the whole database???? > > if you are up to it? or i can call you???? let me know > yes, but i'm not so good with formulas, i don't know what i'm doing wrong, my > database has other columns and information. it's not coming out right i have > a lot of diff dates. What exactly is happening? Are you getting an incorrect result? rosa : > OK: I know there must be an esier way to do this, i'm working with a data > base in wich i have a column of dates and next to it a column with quantities [quoted text clipped - 8 lines] > 6/16/2006 12800 275,000 > want to separete may from june and sum quantities from each month. anybody??? Another formula could be: =SUM(IF(MONTH($A$2:$A$100)=5,$B$2:$B$100,0)) ...in which the dates are in cells A2 down to A100 and the quantities in celles B2 down to B100 (you'll have ot adjust it to your sheet). This formula has to be validated holding down the "Apple" Key while pressing the "Enter" key. When you do that, you'll notice the formula displays with brackets: {=SUM(IF(MONTH($A$2:$A$100)=5,$B$2:$B$100,0))} Of course, you can then easily have the quantities for the other months changing the formula to: =SUM(IF(MONTH($A$2:$A$100)=6,$B$2:$B$100,0)) ...for June, and so on. Notice you'll have to validate holding the "Apple" Key each and everytime you'll edit the cell (and if you don't, the brackets are gone, and the result too). SignatureBernard Rey - Toulouse / France MVP - office:macintosh http://faq.office.macintosh.free.fr My method is in another column on the same row enter the formula =Month(A1) that is if the date is in A1, if date is in B1 change formula to =Month(B1) Then fill down. Now you can filter and subtotal by month On 25/5/06 5:40 PM, in article C09B2BF4.33F8%b.rey@DontSpamMe.mvps.org, > rosa : > [quoted text clipped - 34 lines] > Key each and everytime you'll edit the cell (and if you don't, the brackets > are gone, and the result too). > OK: I know there must be an esier way to do this, i'm working with a data > base in wich i have a column of dates and next to it a column with quantities [quoted text clipped - 8 lines] > 6/16/2006 12800 275,000 > want to separete may from june and sum quantities from each month. anybody??? The easiest way to do this, IMO, is to use a Pivot Table. Use the date in the row field. In your PT, ctrl-click the date field header, choose Group, and group by months. thank you all, i will take you for dinner.. but i don't know where you are :) you've been a great help, have to turn my work today i'll try everything !!!!! > > OK: I know there must be an esier way to do this, i'm working with a data > > base in wich i have a column of dates and next to it a column with quantities [quoted text clipped - 12 lines] > in the row field. In your PT, ctrl-click the date field header, choose > Group, and group by months. The easiest solution I know is to insert a column and use an EOMONTH function, which will return the last day of the month for each record. You may have to activate it using the Data Analysis Add-in (a 10 second process). Assuming your dates are in column A, your formula would be: =EOMONTH(A1,0) and it would return the date code for May 31, 2006. This gets you around the issue of years if your data goes back more than 12 months and you can easily summarize it using a pivot table or the Subtotals function in the Data menu. > thank you all, i will take you for dinner.. but i don't know where you are :) > you've been a great help, have to turn my work today i'll try everything !!!!! [quoted text clipped - 15 lines] > > in the row field. In your PT, ctrl-click the date field header, choose > > Group, and group by months. > The easiest solution I know is to insert a column and use an EOMONTH > function, which will return the last day of the month for each record. You > may have to activate it using the Data Analysis Add-in (a 10 second process). Not all companies or institutions allow installation of the Analysis Toolpak Add-in. For those that don't (or if you just don't want to use that add-in), an equivalent to =EOMONTH(A1, n) is =DATE(YEAR(A1), MONTH(A1) + n + 1, 0) This works since, to XL, the "zeroth" day of any month is the last day of the previous month. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.