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

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

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
17,017
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,864
Messages
5,834,057
Members
430,260
Latest member
MANICX100

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