Three months data

adam · Post by **adam** » 15 May 2016, 06:25

The following formula sum ups all the data rows from the sheet referenced in the formula to the sheet where the formula is placed.
=SUMIF(NewData!D:D,F10, NewData!E:E)

I want to place this formula in Sheet "Request" so that it would sum up rows of data only from the last three months.

I have date in cell G6 of Sheet "Request".

I have placed date in column B of the sheet where data is to be taken.

Any help on this would be kindly appreciated.

Post by **Rudi** » 15 May 2016, 06:31

Is the date in G6 to be used as criteria to determine the last three months that must be summed or must this date range be calculated from the current date?

adam · Post by **adam** » 15 May 2016, 06:34

The date in G6 is to be used as criteria to determine the last three months that must be summed.

Post by **Rudi** » 15 May 2016, 06:43

I am going by early morning assumptions here:

Try:

=SUMIFS(NewData!E:E, NewData!D:D,"<="&G6,NewData!D:D,">="&DATE(YEAR(G6),MONTH(G6)-3,DAY(G6)))

The formula assumes the range to sum is in column E on the NewData sheet
The formula assumes the range of dates is in the D column of the NewData sheet
The formula uses the date in G6 of the current sheet as the recent data and sums all values between three months prior and up until the date in G6.
Let me know if I have misinterpreted the condition.

AMMENDMENT:
Sorry...in the above formula I changed:
this: "<=G6"
to this: "<="&G6

A small syntax error on my part!

adam · Post by **adam** » 15 May 2016, 08:39

Im sorry I made a mistake. Following is the actual formula.
=SUMIF(NewData!E:E,F9, NewData!F:F)

The range to sum is in column F on the NewData sheet
The range of dates is in the B column of the NewData sheet
The column F of NewData will be sum according to the product name in column E of the sheet NewData.
F9 is the column referenced in the sheet where the formula is placed.

I hope I've made my question clear.

Post by **Rudi** » 15 May 2016, 08:51

Sorry, I'm am a bit confused now...
Is F9 a date or a product name?

adam · Post by **adam** » 15 May 2016, 08:55

F9 of the sheet where the formula is placed is product name.
And also column E of the NewData is product name.

The intention of the formula is to sum up the quantity of the product within the last three months if the names in column E of NewData matches with the name of F9 in the sheet where the formula is placed.

Post by **Rudi** » 15 May 2016, 08:59

TX.

And does G6 still apply? Does it still have the date from which the three month duration must apply, or is the three month duration calculated from the current date?

adam · Post by **adam** » 15 May 2016, 09:03

Yes G6 has the current date and the three month duration is calculated from the current date in G6.

Post by **Rudi** » 15 May 2016, 09:14

This should do...

=SUMIFS(NewData!F:F,NewData!B:B,"<="&G6,NewData!B:B,">="&DATE(YEAR(G6),MONTH(G6)-3,DAY(G6)),NewData!E:E,F9)

adam · Post by **adam** » 15 May 2016, 09:32

Thanks Rudi the formula works now.

But there's a query I want to clear.
Lets say for example Today's date is 5/15/2016
If we consider three months it would be like, 5/15/2016 to 4/15/2016 is one month. 4/15/2016 to 3/15/2016 is two months and 3/15/2016 to 2/15/2016 is three months.

If so why does the formula still sum up dates like 1/10/2016?

Post by **HansV** » 15 May 2016, 09:45

@Rudi: instead of DATE(YEAR(G6),MONTH(G6)-3,DAY(G6)) you can use EDATE(G6,-3)

Post by **HansV** » 15 May 2016, 09:49

@adam: Rudi's formula should return the correct result. I tested it, and it sums only data for which the date is between today - 3 months (15 February 2016) and today (15 May 2016). Are you sure that G6 contains today's date?

Post by **Rudi** » 15 May 2016, 10:05

Thanks Hans, EDATE(...) does clean up the syntax nicer.

Hi Adam,

I did update the formula about 30 seconds after I posted it as I was testing the formula on a date range holding a year of data. I spoted it almost immediately after I posted it and had to return to correct it!

I suspect that you grabbed the formula within the 30 seconds of me posting it... (You were too quick for me!) I had to rectify the part from DATE(YEAR(G6)-1,MONTH(G6),DAY(G6)) (a year range) to DATE(YEAR(G6),MONTH(G6)-3,DAY(G6)) (a 3 month range). However, Hans's suggestion to use EDATE(G6,-3) is easier.

adam · Post by **adam** » 15 May 2016, 10:06

Yes I have applied the formula =TODAY() in G6

adam · Post by **adam** » 15 May 2016, 10:09

Sorry I does work fine now. Actually there was a date shuffling in column B of the NewData sheet. When I arranged the dates in ascending order the formula works fine.

Post by **Rudi** » 15 May 2016, 10:10

Use this as a correct version:

=SUMIFS(NewData!F:F,NewData!B:B,"<="&G6,NewData!B:B,">="&EDATE(G6,-3),NewData!E:E,F9)

Post by **HansV** » 15 May 2016, 10:12

It shouldn't matter whether the dates in column B are in ascending order or not...

adam · Post by **adam** » 17 May 2016, 05:59

I've applied your final version Rudi. Hans, May be I had a formatting mistake in the column where date existed.

Post by **HansV** » 17 May 2016, 06:22

So everything is OK now? Good to hear that.

Eileen's Lounge

Three months data

Three months data

Re: Three months data

Re: Three months data

Re: Three months data

Re: Three months data

Re: Three months data

Re: Three months data

Re: Three months data

Re: Three months data

Re: Three months data

Re: Three months data

Re: Three months data

Re: Three months data

Re: Three months data

Re: Three months data

Re: Three months data

Re: Three months data

Re: Three months data

Re: Three months data

Re: Three months data