Results 1 to 4 of 4
  1. #1
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    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.
    - Ricky

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formula / Date / End of Month (XL2003)

    See attached for the Best Monday etc.

  4. #4
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Thank You

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

Posting Permissions

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