Getting sum of values in another sheet while using condition

[ser_man]

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

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[Claus Busch]

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

 

[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, bu
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 th
begining

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[Claus Busch]

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

 

[Living the Dream]

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.

 

[Living the Dream]

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