Tech Support Guy banner
Status
Not open for further replies.
1 - 7 of 7 Posts

·
Registered
Joined
·
5 Posts
Discussion Starter · #1 ·
I've got a table with dates in one column and associated values in another.
What I need is a formula that will do the following:
Search down through the date column, find dates that are between two other dates kept in another two cells and sum those values.
Example (in it's simplest form)

Start Date 1/3/2009
End Date 1/10/2009
Total Value 32.50

1/3/2009
1/4/2009
1/5/2009 12.93
1/6/2009
1/7/2009 9.32
1/8/2009 10.25
1/9/2009
1/10/2009
1/11/2009
1/12/2009 2.56
1/13/2009
1/14/2009

The formula I am looking for is the one located to the right of "Total Value" that is shown with 32.50.

Any help would be appreciated,

Craig
 

·
Registered
Joined
·
439 Posts
Try this

=SUM(IF(AND(A1:A12>StartDateCell, A1:A12<EndDateCell),B1:B12))

You need to enter the formula, and then instead of pressing enter, to complete the formula, hold Ctl + Alt, and press enter (this will put {} around your formula).

StartDateCell & EndDateCell should be the cell reference to where these values are stored.

Assumption: Data tables is in Cell range A1:B12,
 

·
Registered
Joined
·
5 Posts
Discussion Starter · #3 ·
I tried the formula that you provided and it didn't seem to give me any results. I had to modify the formula to meet my current needs and may have caused the failure. Here is my implementation:

Daily Value Weekly Total
12/27/2008
1/3/2009 1/3/2009 0
1/4/2009 1/10/2009 0
1/5/2009 12.93 1/17/2009 0
1/6/2009 1/24/2009 0
1/7/2009 9.32 1/31/2009 0
1/8/2009 10.25 2/7/2009 0
1/9/2009 2/14/2009 0
1/10/2009 2/21/2009 0
1/11/2009 2/28/2009 0
1/12/2009 2.56 3/7/2009 0
1/13/2009 3/14/2009 0
1/14/2009 3/21/2009 0

In the column under total, currently zeros, I have the following formulas:

=SUM(IF(AND(A$3:A$401>C2,A$3:A$401<C3),B$3:B$401))
=SUM(IF(AND(A$3:A$401>C3,A$3:A$401<C4),B$3:B$401))
=SUM(IF(AND(A$3:A$401>C4,A$3:A$401<C5),B$3:B$401))
Etc.

I have seen a note saying that Excel 2003 can have only one condition, but I don't know if that applies to the array formulas.

Thanks for your help

Craig
 

·
Registered
Joined
·
5 Posts
Discussion Starter · #4 ·
Sorry. the editor does not maintain the spacing that I inserted in the table.
Let's see if "Hard Spaces" work any different:
Daily Value Weekly Total
12/27/2008
1/3/2009 1/3/2009 0
1/4/2009 1/10/2009 0
1/5/2009 12.93 1/17/2009 0
1/6/2009 1/24/2009 0
1/7/2009 9.32 1/31/2009 0
1/8/2009 10.25 2/7/2009 0
1/9/2009 2/14/2009 0
1/10/2009 2/21/2009 0
1/11/2009 2/28/2009 0
1/12/2009 2.56 3/7/2009 0
1/13/2009 3/14/2009 0
1/14/2009 3/21/2009 0

Craig
 

·
Registered
Joined
·
5 Posts
Discussion Starter · #5 ·
Guess not. Here it is with Underlines

Daily________Value________Weekly________Total
________________________12/27/2008________
1/3/2009___________________1/3/2009________0
1/4/2009__________________1/10/2009________0
1/5/2009________12.93______1/17/2009________0
1/6/2009__________________1/24/2009________0
1/7/2009________9.32_______1/31/2009________0
1/8/2009________10.25______2/7/2009________0
1/9/2009__________________2/14/2009________0
1/10/2009__________________2/21/2009________0
1/11/2009__________________2/28/2009________0
1/12/2009________2.56_______3/7/2009________0
1/13/2009__________________3/14/2009________0
1/14/2009__________________3/21/2009________0


Craig
 

·
Registered
Joined
·
331 Posts
I've got a table with dates in one column and associated values in another.
What I need is a formula that will do the following:
Search down through the date column, find dates that are between two other dates kept in another two cells and sum those values.
Example (in it's simplest form)

Start Date 1/3/2009
End Date 1/10/2009
Total Value 32.50

1/3/2009
1/4/2009
1/5/2009 12.93
1/6/2009
1/7/2009 9.32
1/8/2009 10.25
1/9/2009
1/10/2009
1/11/2009
1/12/2009 2.56
1/13/2009
1/14/2009

The formula I am looking for is the one located to the right of "Total Value" that is shown with 32.50.

Any help would be appreciated,

Craig
Try,

=SUMPRODUCT(--(A1:A10>=X1),--(A1:A10<=X2),B1:B10)

Where X1 and X2 houese date values.
 
1 - 7 of 7 Posts
Status
Not open for further replies.
Top