SUMPRODUCT Between Dates

[JerryS]

I'm looking for a formula that will add the total number of units in one
column that fall between dates of another column. Dates are shown as
01/01/2009 for example and I am looking to sum units between 01/01/2009 and
01/31/2009. Thanks

 

[Marcelo]

=SUMPRODUCT(--(D32101>=DATE(2009,1,1))*--(D32101<=DATE(2009,1,31)),E32:E101)

ajust the range for your needs.
hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"JerryS" escreveu:

 

[RagDyeR]

Assuming dates in Column A are "legal" XL dates,
and units are in Column B, with start date entered in C1 and end date
entered in C2:

=Sumproduct((A2:A100>=c1)*(A2:A100<=C2)*B2:B100)


--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

I'm looking for a formula that will add the total number of units in one
column that fall between dates of another column. Dates are shown as
01/01/2009 for example and I am looking to sum units between 01/01/2009 and
01/31/2009. Thanks

 

[Bernard Liengme]

Marcelo,
Double negation is used in SUMPRODUCT to convert FALSE/TRUE Boolean values
to numeric 0/1 values. Excel does this whenever an arithmetic operation is
performed on a Boolean. But you are also multiplying, so the double
negation is not needed. It is not wrong, just unnecessary.

Either of these will work
=SUMPRODUCT((D32101>=DATE(2009,1,1))*(D32101<=DATE(2009,1,31)),
E32:E101)
=SUMPRODUCT(--(D32101>=DATE(2009,1,1)), --(D32101<=DATE(2009,1,31)),
E32:E101)
best wishes

 

[Marcelo]

thank you Bernard, I really appreciate your tips.

best wishes
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Bernard Liengme" escreveu:

 

[chappy]

Jerry, if it were me, I would use the "Sum If" function looking at a value or
date greater than or = to one date and less than or equal to the other

Chappy

 

[Ron Rosenfeld]

On Tue, 20 Jan 2009 07:37:15 -0800, JerryS <Jerry B

I'm looking for a formula that will add the total number of units in one
column that fall between dates of another column. Dates are shown as
01/01/2009 for example and I am looking to sum units between 01/01/2009 and
01/31/2009. Thanks

A1: 01/01/2009
A2: 01/31/2009

dates: your range of cells containing the dates (e.g. B1:B1000)
units: your range of cells containing the numbers of units (e.g. C1:C1000)

=SUMIF(dates,">="&A1,units) - SUMIF(dates,">"&A2,units)
--ron

 

[EMB369]

=SUMPRODUCT(('Case Log'!H:H>=$I$8)*('Case Log'!H:H<=$I$9))

I'm using the formula (above). $I$8 is a user specified start date and $I$9 is a user specified end date (I copied this formula to other places that's why I used the $s).

The goal is to look at the column H - which is populated with dates and find the total number of occurrences of dates between two user specified dates. Just need a simple count essentially.

The issue is that it works perfectly well in Excel 2007 - which I use - however, it does not work in Excel 2003. In Excel 2003 it gives a #NUM! error and I can't figure out why.

Thanks in advance for any suggestions!