SUMIF by date range (Excel 2003)

Topic

New Reply

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!

Reply | Quote

Replies

(Edited by sdckapr on 16-Sep-08 13:42. Added PS)

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

Reply | Quote

Great, thanks! This really helps me out and will save lots of time…

Reply | Quote

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

Reply | Quote

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

Reply | Quote