Sum based on value between two cells
 

Hello,
I have 3 fields, Date, OldestDate and Received. I need to sum the values of
Received between, when Date = Oldestdate. For instance below:

I want to sum the range (Received) between 1/12/2009 in the Date field and
1/12/09 in the OldestDate field for a total of 558.


Date OldestDate Received
1/12/2009 12/23/2008 208
1/13/2009 12/23/2008 20
1/14/2009 12/23/2008 44
1/15/2009 12/23/2008 81
1/16/2009 12/23/2008 42
1/19/2009 1/12/2009 163

Thanks.


--
Sherry N.

Sum based on value between two cells
 

With the query date in D1 try the below formula

=SUM(OFFSET(C1,MATCH(D1,A:A,0)-1,0,MATCH(D1,B:B,0)-MATCH(D1,A:A,0)+1,1))

If this post helps click Yes
---------------
Jacob Skaria


"Sherry N." wrote:

Hello,
I have 3 fields, Date, OldestDate and Received. I need to sum the values of
Received between, when Date = Oldestdate. For instance below:

I want to sum the range (Received) between 1/12/2009 in the Date field and
1/12/09 in the OldestDate field for a total of 558.


Date OldestDate Received
1/12/2009 12/23/2008 208
1/13/2009 12/23/2008 20
1/14/2009 12/23/2008 44
1/15/2009 12/23/2008 81
1/16/2009 12/23/2008 42
1/19/2009 1/12/2009 163

Thanks.


--
Sherry N.

Sum based on value between two cells
 

Sumproduct will also work on this

=SUMPRODUCT(--(A2:A712/1/9),(B2:B712/1/9)*C2:C7)



"Sherry N." wrote:

Hello,
I have 3 fields, Date, OldestDate and Received. I need to sum the values of
Received between, when Date = Oldestdate. For instance below:

I want to sum the range (Received) between 1/12/2009 in the Date field and
1/12/09 in the OldestDate field for a total of 558.


Date OldestDate Received
1/12/2009 12/23/2008 208
1/13/2009 12/23/2008 20
1/14/2009 12/23/2008 44
1/15/2009 12/23/2008 81
1/16/2009 12/23/2008 42
1/19/2009 1/12/2009 163

Thanks.


--
Sherry N.

Sum based on value between two cells
 

On the worksheet use

=SUMPRODUCT(--(A2:A7<=DATEVALUE("1/12/09")),--(A2:A7=DATEVALUE("1/12/09")),C2:C7)


In VABA use
MyTotal =
evalute("SUMPRODUCT(--(A2:A7<=DATEVALUE(""1/12/09"")),--(A2:A7=DATEVALUE(""1/12/09"")),C2:C7)")


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=147914