Help with changing range formula

[Aidie80]

I have a date that is entered in one cell, and I want a formula that can change the range depending on this value. Here is a very simple illustration of what I am looking for (in reality, I am using XNPV so would be using it for both the date range and data range).

The Total cell is a sum of the range that starts with the date that matches the date at the top, and then goes to the bottom.

So in the first example, the formula would add cells B4:B7

January 1, 2013
Total	70,000
January 1, 2013	10,000
February 1, 2013	20,000
March 1, 2013	15,000
April 1, 2013	25,000

<tbody>
</tbody>

In the second example, the formula would add cells B6:B7

March 1, 2013
Total	40,000
January 1, 2013	10,000
February 1, 2013	20,000
March 1, 2013	15,000
April 1, 2013	25,000

<tbody>
</tbody>

Thanks for any help or ideas!

 

[c_m_s_jr]

Assuming the date you want to start with is in A1 and your range of dates you want to sum is A4:B7 then in B2 put:

=SUM(INDIRECT("B"&(MATCH(A1,$A$4:$A$7,0)+3)&":"&"B"&(COUNTA(A4:A7)+3)))

The MATCH function will find the date in A1 and then I am adding 3 so my starting position is row 4
The COUNTA function will count how many rows are in the range getting me to the "bottom" of the range. Again I add 3 so my ending position will be row 7.
I then concatenate the values found with be to create the range B6:B7
The INDIRECT function makes it so the B6:B7 can be used by the SUM function.

 

[Marcelo Branco]

Are the dates in column A real dates (custom formatted as mmmm d, yyyy), not Text?

If so, try

=SUMIF($A$4:$A$100,">="&A1,$B$4:$B$100)

To check if the values in column A are real dates in an empty cell put
=ISNUMBER(A1)
copy down

If the formulas return TRUE, they are real dates and the formula above should work.

M.

 

[Aidie80]

Thanks CMS Jr!! That works perfectly and I have now converted it to an NPV formula.