MacMost Q&A Forum • View All Forum QuestionsAsk a Question

# How To Do a Spreadsheet Sum Over At Changing Date Range?

i wish to create a spreadsheet
to show a bank balance of my daposits and withdralals over the last 12 months

ie date – amount give me total over last 12 months only and update balance as the date changes

but how do i create a sum = columns range based upon date = now thru 12months ago

so

6-1-2017 deposit \$1000
06-28-2018 deposit \$1000
07-29-2018 withdrawal \$1

i want total over last 12 months
so ignore 6-1-2017 entry only use other 2 dates (valid since < 12 months) i dont see how to write fx to get \$999 if i view on 8-1-2018 and get \$1 if i view on 7-1-2019 i want. total over last 12 months ----- david hold

### Comments: One Response to “How To Do a Spreadsheet Sum Over At Changing Date Range?”

2 years ago

So if the date is in column A, and the amount is in column C, create a column D that is the duration between now and the date. The formula would look like this in cell D2:

=TODAY()−\$A2

The result would look something like “186d” to indicate that it is 186 days old.

Then use SUMIFS to calculate the sum of all of the items by age. For instance, this would give you the sum of all items that are less than 180 days old.

=SUMIFS(C,D,”<180d")