Sum Column Entries that fall within Date Range Listed In Rows
 

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?

Sum Column Entries that fall within Date Range Listed In Rows
 

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

Sum Column Entries that fall within Date Range Listed In Rows
 

Try this:

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

--

HTH,

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

"Ben" wrote in message
oups.com...
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?

Sum Column Entries that fall within Date Range Listed In Rows
 

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

Sum Column Entries that fall within Date Range Listed In Rows
 

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