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
--
JerryS

=SUMPRODUCT(--(D32:D101=DATE(2009,1,1))*--(D32:D101<=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:

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
--
JerryS

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!
================================================== ===

"JerryS" <Jerry B wrote in message
...
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
--
JerryS

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((D32:D101=DATE(2009,1,1))*(D32:D101<= DATE(2009,1,31)),
E32:E101)
=SUMPRODUCT(--(D32:D101=DATE(2009,1,1)), --(D32:D101<=DATE(2009,1,31)),
E32:E101)
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Marcelo" wrote in message
...
=SUMPRODUCT(--(D32:D101=DATE(2009,1,1))*--(D32:D101<=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:

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
--
JerryS

thank you Bernard, I really appreciate your tips.

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



"Bernard Liengme" escreveu:

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((D32:D101=DATE(2009,1,1))*(D32:D101<= DATE(2009,1,31)),
E32:E101)
=SUMPRODUCT(--(D32:D101=DATE(2009,1,1)), --(D32:D101<=DATE(2009,1,31)),
E32:E101)
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Marcelo" wrote in message
...
=SUMPRODUCT(--(D32:D101=DATE(2009,1,1))*--(D32:D101<=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:

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
--
JerryS

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

"JerryS" wrote:

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
--
JerryS

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

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