Thread: SUMIF by date range (Excel 2003)

1. SUMIF by date range (Excel 2003)

Greetings,

I have a question regarding if it is possible to SUMIF (or some other function) by a date range. I track invoices for my company, and I do a lot of manual summing of columns to view invoice quantities by part number by date range. For example, if you look at the attachment, you'll see that for each part in column B, I am manually summing date ranges (shown in row 4) but putting basic sum functions in columns AS-AV. Since I have to do this for many tabs, i would like to know if it's possible to create a formula to Sum the quantities by a given date range (column 4), instead of manually inputting the sum formulas,

Thanks!

2. Re: SUMIF by date range (Excel 2003)

<P ID="edit" class=small>(Edited by sdckapr on 16-Sep-08 13:42. Added PS)</P>in AS8:
=SUMIF(\$J\$4:\$AR\$4,">="&DATE(2008,7,1),\$J8:\$AR8)-SUMIF(\$J\$4:\$AR\$4,">"&DATE(2008,7,31),\$J8:\$AR8)

In AT8:
=SUMIF(\$J\$4:\$AR\$4,">="&DATE(2008,8,1),\$J8:\$AR8)-SUMIF(\$J\$4:\$AR\$4,">"&DATE(2008,8,31),\$J8:\$AR8)

in AU8:
=SUMIF(\$J\$4:\$AR\$4,">="&DATE(2008,9,1),\$J8:\$AR8)-SUMIF(\$J\$4:\$AR\$4,">"&DATE(2008,9,5),\$J8:\$AR8)

In AV8:
=SUMIF(\$J\$4:\$AR\$4,">="&DATE(2008,9,6),\$J8:\$AR8)-SUMIF(\$J\$4:\$AR\$4,">"&DATE(2008,9,12),\$J8:\$AR8)

Copy down the rows.

If desired you can put the start and end dates in 2 cells and reference the cells instead of using the DATE functions. The start and end dates can use the DATE function. You could even have the cell in AU/AV5 read the dates and display the range.

Steve

PS for example if the start dates are in row 1 and the end dates are in row2 you could put in AS8:
=SUMIF(\$J\$4:\$AR\$4,">="&AS\$1,\$J8:\$AR8)-SUMIF(\$J\$4:\$AR\$4,">"&AS\$2,\$J8:\$AR8)
Copy this to AT8:AV8

Then copy AS8:AV8 down the rows.

In AS5:
=IF(AND(YEAR(AS1)=YEAR(AS2),MONTH(AS1)=MONTH(AS2), DAY(AS1)=1,DAY(AS2)=DAY(DATE(YEAR(AS2),MONTH(AS2)+ 1,0))),TEXT(AS1,"mmmm"),TEXT(AS1,"mmm d")&" - "&TEXT(AS2,"mmm d"))
And copy this to AT5:AV5 to read the ranges

3. Re: SUMIF by date range (Excel 2003)

Great, thanks! This really helps me out and will save lots of time... <img src=/S/blackteeth.gif border=0 alt=blackteeth width=20 height=20>

4. Re: SUMIF by date range (Excel 2003)

Steve,

This has really been a great help. What I did was create a separate worksheet that pulls the info from the source with all dates, quantities, etc. However, whenever I edit the link to update the separate report, it fills the information in date format in the cells. Even if i format the cells to general, it reverts back to date format if I update (or even key "enter") when in a cell. have you seen this before? Any thing I can do about it?

Thanks again,

Pete

5. Re: SUMIF by date range (Excel 2003)

Format the cells to a number rather than to general. If formatted to general, excel will try to guess the format desired. Since there are date functions in the formula and you are comparing dates, XL presumes you want the result as a date. If you explicitly give it a format, it won't change it.

Steve

Posting Permissions

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