|
SUMPRODUCT Between Dates
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 Between Dates
=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 |
SUMPRODUCT Between Dates
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 |
SUMPRODUCT Between Dates
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 |
SUMPRODUCT Between Dates
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 |
SUMPRODUCT Between Dates
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 |
SUMPRODUCT Between Dates
|
| All times are GMT +1. The time now is 07:24 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com