Sum Column Entries that fall within Date Range Listed In Rows

[Ben]

I'm trying to sum a list of values in a column, where the date
identifed in each row is >= Now. As example:

7/10/06 100.00
7/11/06 200.00
7/12/06 150.00
7/13/06 250.00

My formula (in theory) needs to be something like:

=if(a1>=Now(),sum(b1:b4)

If Now = 7/12/06, then I need it to return the sum of 7/12 and 7/13 for
a total of 400.00.
If Now = 7/13/06, then I need it to return the sum of 7/13 for a total
of 250.00.

My formual will ultimately be more complicated than this as between
each date there are 5 rows of information. The date cell is a merge of
these 5 cells, and then each row has values totaling up to 100.00,
200.00 etc., so I'll need to then get these extra values subtracted
out.

I think I can do the last part, if I can just fugure out how to capture
only those values >= Now.

Any suggestions?

[Franz Verga]

Ben wrote:
> I'm trying to sum a list of values in a column, where the date
> identifed in each row is >= Now. As example:
>
> 7/10/06 100.00
> 7/11/06 200.00
> 7/12/06 150.00
> 7/13/06 250.00
>
> My formula (in theory) needs to be something like:
>
> =if(a1>=Now(),sum(b1:b4)
>
> If Now = 7/12/06, then I need it to return the sum of 7/12 and 7/13
> for a total of 400.00.
> If Now = 7/13/06, then I need it to return the sum of 7/13 for a total
> of 250.00.
>
> My formual will ultimately be more complicated than this as between
> each date there are 5 rows of information. The date cell is a merge
> of these 5 cells, and then each row has values totaling up to 100.00,
> 200.00 etc., so I'll need to then get these extra values subtracted
> out.
>
> I think I can do the last part, if I can just fugure out how to
> capture only those values >= Now.
>
> Any suggestions?


Hi Ben,

You can use the SUMPRODUCT function:

=SUMPRODUCT((A1:A4)>=TODAY())*(B1:B4))

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

[RagDyeR]

Try this:

=SUMIF(A1:A4,">="&TODAY(),B1:B4)

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

"Ben" <[email protected]> wrote in message
news:[email protected]...
I'm trying to sum a list of values in a column, where the date
identifed in each row is >= Now. As example:

7/10/06 100.00
7/11/06 200.00
7/12/06 150.00
7/13/06 250.00

My formula (in theory) needs to be something like:

=if(a1>=Now(),sum(b1:b4)

If Now = 7/12/06, then I need it to return the sum of 7/12 and 7/13 for
a total of 400.00.
If Now = 7/13/06, then I need it to return the sum of 7/13 for a total
of 250.00.

My formual will ultimately be more complicated than this as between
each date there are 5 rows of information. The date cell is a merge of
these 5 cells, and then each row has values totaling up to 100.00,
200.00 etc., so I'll need to then get these extra values subtracted
out.

I think I can do the last part, if I can just fugure out how to capture
only those values >= Now.

Any suggestions?

[SteveG]

Ben,

Try,

=SUMPRODUCT((A1:A4>=TODAY())*(B1:B4))

Use TODAY instead of NOW unless you need to evaluate the time and not just the date. NOW() will return date and time so in your sample data the above formula will exclude the value for 7/10/2006 from the sum because the default time when you enter a date is 12:00 AM which is not greater than or equal to NOW().


HTH

Steve

[Ben]

Thanks everyone for your help. Using Today instead of Now helped, and
both SumIF and SumProduct worked. My problem was in the more complex
portion of my post, where I said each date had 5 rows and I merged the
5 cells together for the date value...then tryed to read and sum the
second column. I think Excel needed a 1 to 1 relationship between date
cell and my second column in order to sum the values, so removing the
merge corrected the problem. Once I did this and put the holding date
in the 5th row at each occurence, the calculations worked.

Thanks again for your help!!!!




SteveG wrote:
> Ben,
>
> Try,
>
> =SUMPRODUCT((A1:A4>=TODAY())*(B1:B4))
>
> Use TODAY instead of NOW unless you need to evaluate the time and not
> just the date. NOW() will return date and time so in your sample data
> the above formula will exclude the value for 7/10/2006 from the sum
> because the default time when you enter a date is 12:00 AM which is not
> greater than or equal to NOW().
>
>
> HTH
>
> Steve
>
>
> --
> SteveG
> ------------------------------------------------------------------------
> SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
> View this thread: http://www.excelforum.com/showthread...hreadid=559945