| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Conditional Formatting Based of Cells Based on Data Entry in anoth | Excel Discussion (Misc queries) | |||
| display a range of cells editible cells based on matching date | Excel Worksheet Functions | |||
| conditional formating cells i Excel based on other cells values | Excel Worksheet Functions | |||
| Fill cells with color based on criteria in two cells | Excel Worksheet Functions | |||
| Auto populate cells based on 2 cells division. | Excel Discussion (Misc queries) | |||
Linear Mode
