# Help with changing range formula

#### Aidie80

##### New Member
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!

### Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
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.

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.

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

Replies
1
Views
371
Replies
1
Views
129
Replies
1
Views
121
Replies
3
Views
158
Replies
0
Views
61

1,217,756
Messages
6,138,428
Members
450,136
Latest member
Tabako1960

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back