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!
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

c_m_s_jr

Well-known Member
Joined
Mar 23, 2009
Messages
1,561
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

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,407
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

New Member
Joined
Apr 11, 2013
Messages
2
Thanks CMS Jr!! That works perfectly and I have now converted it to an NPV formula.

(y)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,576
Messages
5,596,992
Members
414,115
Latest member
SFUser

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
Top