Billy Pang
tuxedoman888 at gmail.com
Wed Feb 21 17:15:32 CST 2007
ok, i think there is a way that it will probably work but only if the dates are ALWAYS in consecutive order... here are the steps for illustration.. 1) use your dataset example (6 rows) where 01-28-2007 is on cell A3 and 400 is on cell B3 and so on... 2) for column c, use the concatenated value of month and year (ie. =MONTH(A3) & YEAR(A3) for cell c3) 3) for column d, use the formula [=IF(C3=C2,B2+B3,B3)] for cell d3; what that formula does is determine whether there is need to create running sum or just take record total based on whether the concatentated value is the same as the previuos row above. 4) fill down the formulas from steps 2 and 3 above down to row 8 (beacuse there are 6 rows and we started on row 3) performing the following steps, for column d, it will show the following values in the following order: 400, 600, 300, 700, 1200, 100 Billy On 2/20/07, Susan Harkins <ssharkins at setel.com> wrote: > > There are no labels, so subtotals doesn't work. > > In column B I have general dates d/m/y -- these dates are in consecutive > order. > In column G I have values. > > I want to keep a running total of the values in column G by the month > (column B). > B G > 1/28/2007 400 400 > 1/30/2007 200 600 > 2/3/2007 300 300 > 2/4/2007 400 700 > 2/28/2007 500 1200 > 3/4/2007 100 100 > > Thanks to you and Billy, but so far, I haven't gotten it right. > > Susan H. > > Assuming dates are I A1 and B1, and data top add is in A2 and B2... > > Try =IF(MATCH(C1,D1,1)=1,SUM(A2,B2),"") > > > Jon > > -----Original Message----- > From: dba-tech-bounces at databaseadvisors.com > [mailto:dba-tech-bounces at databaseadvisors.com] On Behalf Of Billy Pang > Sent: 20 February 2007 23:32 > To: Discussion of Hardware and Software issues > Subject: Re: [dba-Tech] Excel formula > > Concatenate the values returned by MONTH and YEAR (eg. =YEAR(A5) & > MONTH(A5)) for the sumif? > > Or if the datasource is sorted by date, then perhaps the subtotal by the > concatentate value mentioned above (ie. FILE --> DATA --> SUBTOTAL...)? > > Billy > > On 2/20/07, Susan Harkins <ssharkins at setel.com> wrote: > > > > I'm trying to use SUMIF to keep a running total of monetary values, by > > a date value in another column. I think SUMIF's the way to go, but I'm > > not getting it right -- I don't know how to tell the formula to use > > just the Month component of the date. > > > > I have two columns: Date and Fee. I want to see a third column that > > sums the values in Fee when the month and year in Date are the same. > > > > Anyone know how to do that? > > > > Susan H. > > > > _______________________________________________ > > dba-Tech mailing list > > dba-Tech at databaseadvisors.com > > http://databaseadvisors.com/mailman/listinfo/dba-tech > > Website: http://www.databaseadvisors.com > > > > > > -- > Billy Pang > http://dbnotes.blogspot.com/ > "Once the game is over, the King and the pawn go back in the same box." - > Italian proverb _______________________________________________ > dba-Tech mailing list > dba-Tech at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-tech > Website: http://www.databaseadvisors.com > > -- > This email has been verified as Virus free Virus Protection and more > available at http://www.plus.net > > _______________________________________________ > dba-Tech mailing list > dba-Tech at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-tech > Website: http://www.databaseadvisors.com > > -- > No virus found in this incoming message. > Checked by AVG Free Edition. > Version: 7.5.441 / Virus Database: 268.18.3/694 - Release Date: 2/20/2007 > 1:44 PM > > > _______________________________________________ > dba-Tech mailing list > dba-Tech at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-tech > Website: http://www.databaseadvisors.com > -- Billy Pang http://dbnotes.blogspot.com/ "Once the game is over, the King and the pawn go back in the same box." - Italian proverb