# Thread: Formula / Date / End of Month (XL2003)

1. ## Formula / Date / End of Month (XL2003)

I need assistance with two different formulas, please. I have a table where every date from May 2002 through the present is listed down the B column. The sales for each day is listed down the C column. Column H is where the monthly sales totals will be calculated. That means column H will only have an amount listed on the same row where B has a month-ending date.

I need a formula in column H that will determine if the date in the B column is the last day of the month and if so, return the summed dollar amount from all the values in column C that occurred during the given month. Clear as mud? I've entered some sample data on the attached sheet.

Also, if I could get some help deriving the "Best Monday" from the list. I could take it from there.

2. ## Re: Formula / Date / End of Month (XL2003)

Enter the following formula in H6:

=IF(DAY(B6+1)=1,SUMPRODUCT(\$D\$6:\$D\$6000*(YEAR(\$B\$6 :\$B\$6000)=YEAR(B6))*(MONTH(\$B\$6:\$B\$6000)=MONTH(B6) )),"")

and fill down as far as needed. The 6000 that occurs 3 times in the formula is an arbitrary row number below the end of the table.

3. ## Re: Formula / Date / End of Month (XL2003)

See attached for the Best Monday etc.

4. ## Thank You

I really appreciate your help. <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•