Copy & paste data if today is within X days of Y date

[gwoolley0302]

Hello there,

I'm looking to use a sumifs formula, and copy and paste (and 'store') the summed value if its the relevant time to do so.

I have attached a sample sheet. On the left is a sample table, with column C containing the values I'd like to aggregate by 'company' (column A). The issue I have is that values in column C are overwritten with updated values every quarter. Therefore I'm trying to copy and paste what the aggregated values are every 3 months (before they're overwritten) and store them according to their relevant quarter - see table on the right side.

So I'm wondering if a sumifs formula could include something like an if statement which assesses - 'if today is within 30 days of the 'Data check date' (G2:M2) then complete the neccessary sumifs formula. And store as a value so it doesn't change going forward, when data is overwritten'

I would want to copy the formula into future quarters but leave as blank until the relevant time.

Is this possible (without VBA)?

Thanks for your help.

Greg

[AliGW]

There are no dates in the source table, so how do you propose calculating this?

Please add at lest a few expected results to the yellow area. Thanks.

[gwoolley0302]

Hi @AliGW

Appreciate the response.

Please see attached a v2 of the sample file with some expected results.

I'm proposing to calculate by today's date, i.e. the date the file is opened.

In the new file I've added a second source table just to exemplify how the data might look in 3 months. However, to be clear, there actually only one source table, that gets updated every 3 months. I'm attempting to extract data every quarter before the update (and opverwrite) occurs.

Does that help?

Greg

[gwoolley0302]

[Bump]

Hello, would anyone be able to help with this query please?

Thank you in advance.

[AliGW]

Unless the rows are dated, you cannot filter the data and sum it for the summaries, as Excel won't know what's new and what isn't.

[gwoolley0302]

Thanks for the reply @AliGW.

So would it be possible if the source data has a column which has a date column that is updated each time the data is updated? (See attached updated file)

With this addition, are there any ways for the Excel to 'capture' repayments numbers within a certain time period (e.g. within 30 days of a 'data check date') - e.g. automatically copy and paste them into a quarter by quarter table, before the data gets overwritten?

I would want to copy the formula into future quarters but leave as blank until the relevant time.


(In the file I've added a second and third source table (A17:C27 and A30:C40) just to exemplify how the data looked like 3 months ago and how it might look in 3 months. However, to be clear, there is actually only one source table, that gets updated every 3 months. I'm attempting to extract data every quarter before the update (and overwrite) occurs.)