Help with changing range formula

Aidie80

New Member
Joined
Apr 11, 2013
Messages
2
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
Total70,000
January 1, 201310,000
February 1, 201320,000
March 1, 201315,000
April 1, 201325,000

<tbody>
</tbody>

In the second example, the formula would add cells B6:B7
March 1, 2013
Total40,000
January 1, 201310,000
February 1, 201320,000
March 1, 201315,000
April 1, 201325,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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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