# Thread: Formula Help - Range (XL2000 SR-1)

1. ## 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"))}

2. ## 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.

3. ## Re: Formula Help - Range (XL2000 SR-1)

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

- workbook attached.

4. ## Re: Formula Help - Range (XL2000 SR-1)

did you edit your original post?

5. ## 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!

#### Posting Permissions

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