Sum Column Entries that fall within Date Range Listed In Rows

  • Thread starter Thread starter Ben
  • Start date Start date
B

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?
 
Ben said:
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?
Click to expand...


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
 
Try this:

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

--

HTH,

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

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?
 
Ben,

Try,

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

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


HTH

Stev
 
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!!!!
 
Back
Top