Hi, i'm looking to get a sum of values in a range of dates from one sheet and to have the sum in another sheet.
for example i define in the second sheet that the range of dates is 3/4/12 to 3/3/13 The function will lookup the dates range and will sum the values that appear in the 2nd coloum. anyone got any ideas?

for easier understanding, in the picture attached i want to scan only some of the dates range and to sum the 4th coloumn value

Hi,

Am Thu, 21 Mar 2013 13:00:51 +0000 schrieb ser_man:

Hi, i'm looking to get a sum of values in a range of dates from one
sheet and to have the sum in another sheet.
for example i define in the second sheet that the range of dates is
3/4/12 to 3/3/13 The function will lookup the dates range and will sum
the values that appear in the 2nd coloum. anyone got any ideas?

try:
=SUMPRODUCT(--(Sheet1!$A$1:$A$1000=DATE(2012,4,3)),--(Sheet1!$A$1:$A$1000<=DATE(2013,3,3)),Sheet1!$B$1: $B$1000)


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Thank you for your reply; it's been very helpful.
Is there any way that the date won't be defined in the formula, but instead it will be defined by another cell value (for example date(a5)), since the reference to the date is what caused the problem from the begining?

Hi,

Am Sun, 24 Mar 2013 11:58:35 +0000 schrieb ser_man:

Thank you for your reply; it's been very helpful.
Is there any way that the date won't be defined in the formula, but
instead it will be defined by another cell value (for example date(a5)),
since the reference to the date is what caused the problem from the
begining?

start date in A5, end date in A6:
=SUMPRODUCT(--(Sheet1!$A$1:$A$1000=A5),--(Sheet1!$A$1:$A$1000<=A6),Sheet1!$B$1:$B$1000)


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Let's assume

A1 = Start Date
B1 = Finish Date

=SUMPRODUCT(--(Sheet1!$A$2:$A$1000=$A$1),--(Sheet1!$A$2:$A$1000<=$B$1),Sheet1!$B$2:$B$1000))

HTH
Mick.

On 25/03/2013 12:56 AM, Living the Dream wrote:
Let's assume

A1 = Start Date
B1 = Finish Date

=SUMPRODUCT(--(Sheet1!$A$2:$A$1000=$A$1),--(Sheet1!$A$2:$A$1000<=$B$1),Sheet1!$B$2:$B$1000))


HTH
Mick.

What Claus said... :)

Seems I included and extra, unwanted closing bracket, der.....