Sumif function with a date range condition statement

[COskibum]

I'm trying to sum the numbers in one column that fall within specified date
ranges listed in another column i.e. how many products were produced during a
given quarter throughout a fiscal year. The formula I've created looks
something like:

=SUMIF(L7:L226,"=AND(<9/30/2008,>01/01/2009)",M7:M226)

or basically (SUMIF(dates column,date range condition, numbers column to sum))

I suspect that the problem lies within the formatting for the date range
condition, but I haven't been able to find the proper usage for using a date
range within a condition statement. Anyone out there know how to make this
work?

 

[Max]

Try this:
=SUMPRODUCT(((L7:L226<--"30 Sep 2008")+(L7:L226>--"1 Jan
2009"))*(L7:L226<>""),M7:M226)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,000 Files:354 Subscribers:53
xdemechanik

 

[COskibum]

Max,

Thanks, the SUMPRODUCT function solved the problem!

 

[Max]

Welcome, glad it helped.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads: 16,000, Files: 354, Subscribers: 53
xdemechanik

 

[COskibum]

Max,

After entering more dates I realized that the solution you suggested didn't
work as I had envisioned. I did, however, find a work around that may not be
either elegant or efficient, but it works for me. For the work around I
created a series of SUMIF statements extracting the number of products
created through the:

first quarter (10/01 - 12/31)
=SUMIF(dates column range,"<01/01/2008",products column range)

half year
=SUMIF(dates column,"<04/01/2008",products column)

3/4 year
=SUMIF(dates column,"<07/01/2008",products column)

and finally the full year
=SUMIF(dates column,"<09/30/2008",products column)

The SUMIF statements are hidden in my spreadsheet underneath the dates
column. I then simply built a report block that entered the results of the
respective SUMIF statements into the proper quarter followed by subtracting
the SUMIF statement results for the prior quarter(s). For example the SUMIF
statement for 3/4 year extracts all of the products created through quarters
1,2,&3 and I then subtracted the products created in quarters 1 & 2 to arrive
at the number of products created in just quarter 3 and so forth. Not
exactly pretty, but it works.

BTW - I tried countless variants of date ranges in the conditional statement
of the SUMIF function, but could never get the syntax correct and I haven't
been able to find anything usefull in the Microsoft knowledge base.

Thanks for your suggestion and help!