Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
 
Posts: n/a
Default Month to date sales - reset in new month???

Hi all,

I have a column that lists daily sales on an ongoing basis. I need to
create a second column which tracks month to date sales (so it will
grow throughout the month). The catch is I need this column to
automatically reset and start counting again when the daily date
becomes a new month. This must be possible, but I can't figure it out.
Any suggestions? Thanks!

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default Month to date sales - reset in new month???

=SUMPRODUCT(--(YEAR($A$1:A1)=YEAR(A1), (--(MONTH($A$1:A1)=MONTH(A1),
$B$1:B1)

and just copy down

--

HTH

RP
(remove nothere from the email address if mailing direct)


wrote in message
ups.com...
Hi all,

I have a column that lists daily sales on an ongoing basis. I need to
create a second column which tracks month to date sales (so it will
grow throughout the month). The catch is I need this column to
automatically reset and start counting again when the daily date
becomes a new month. This must be possible, but I can't figure it out.
Any suggestions? Thanks!



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
bpeltzer
 
Posts: n/a
Default Month to date sales - reset in new month???

If your columns are Date (A), Sales (B), and MTD Sales (C), with a header row
1, I'd start in cell C2 with =B2. Then in cell C3, =b3 +
if(month(a3)=month(a2),c2,0). In words, the MTD sales through today are
today's sales, plus the prior day's MTD sales as long as we're still in the
same month. Copy that formula from C3 through the rest of column C.
--Bruce

" wrote:

Hi all,

I have a column that lists daily sales on an ongoing basis. I need to
create a second column which tracks month to date sales (so it will
grow throughout the month). The catch is I need this column to
automatically reset and start counting again when the daily date
becomes a new month. This must be possible, but I can't figure it out.
Any suggestions? Thanks!


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Get month from date [email protected] Excel Discussion (Misc queries) 2 November 9th 05 03:43 PM
4 and 5 week months Big Rick Excel Discussion (Misc queries) 15 November 7th 05 01:32 AM
Pulling a date in the current month Brian Excel Worksheet Functions 2 October 24th 05 05:57 PM
Formula for Extracting Month out of a Date column PokerZan Excel Discussion (Misc queries) 3 June 10th 05 08:30 PM
calcuate sales by date and segments Pete Petersen Excel Worksheet Functions 2 January 3rd 05 10:09 PM


All times are GMT +1. The time now is 09:18 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"