SUMPRODUCT Between Dates

J

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
 
M

Marcelo

=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:
 
R

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
 
B

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((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
 
M

Marcelo

thank you Bernard, I really appreciate your tips.

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



"Bernard Liengme" escreveu:
 
C

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
 
R

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top