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

    Formula Help - Range (XL2000 SR-1)

    IN the A column of a sheet, I have several yrs of dates. Entries are posted to this db daily. I want to add more rows to the bottom of the list representing month ending totals.

    Example: Cell A952 will have the date 6/30/2002. I need for cell B952 to be the total of everything in column B in the range above where the A column contains the dates 6/1/2002: 6/30/2002.. Then I could copy the formula to the other columns...

    Not much experience with array formulas but I did try this one and it produced the "0" result and ignored data that was in the range and should've been summed. Guess I didn't do it right? Help???

    {=SUM(IF(AND(MONTH(A952)=MONTH(A2:A949),YEAR(A952) =YEAR(A2:A949)),B2:B949,"0"))}
    - Ricky

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula Help - Range (XL2000 SR-1)

    If I understand what you want, I think it will take two rows of formulas to accomplish what you want. If the first date is in cell A1, then enter the following formula into cell B1500:

    <pre>=DATE(YEAR(A1),MONTH(A1)+1,0)
    </pre>


    That should give you the month end date for the date in cell A1.

    Now enter the following formula into cell C1500:

    <pre>=DATE(YEAR(B1500),MONTH(B1500)+2,0)
    </pre>


    That should give you the month end date for the next month. Fill this formula across the number of columns for which you have months of data. This should give you the month end dates for all of the months.

    Now array enter (hold down Ctrl and Shift when you press enter) the following formula into cell B1501:

    <pre>=SUM(($A$1:$A$1499>=DATE(YEAR(B1500),MONTH(B1 500),1))*($A$1:$A$1499<=B$1500)*$B$1:$B$1499)
    </pre>


    That should give you the sum of the values in column B that are in the first month. Fill this formula across the columns under the month end dates to get the remaining totals.

    I have attached a workbook that demonstrates the formula for 149 rows of data.
    Attached Files Attached Files
    Legare Coleman

  3. #3
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula Help - Range (XL2000 SR-1)

    hmmm. what about this?

    {=SUM((MONTH(A1115)=MONTH(A$2:A$1096))*(YEAR(A1115 )=YEAR(A$2:A$1096))*(B$2:B$1096))}

    - workbook attached.
    Attached Files Attached Files

  4. #4
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula Help - Range (XL2000 SR-1)

    did you edit your original post?

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

    Re: Formula Help - Range (XL2000 SR-1)

    I did edit the original post - the first was rather general with round numbers, etc. I decided to be more specific.

    It would not have altered the solutions, except for cell addresses and I can figure that part out.

    I appreciate the speedy responses from both you and Legare - now I am able to move forward - hopefully no more problems tonight!
    - 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
  •