addition for last 90 days

[Green1972]

Hi All. i have a new question. I have to log my flight time so ive created a excell sheet to do this. as part of the competency i have to calulate totel flight time and flight time for the last 90days. So in column A i have the date (formatted 04/06/18) and in Colum G I have the flight duration (in minutes) for that date. What I want to do in column K is have the cell show the total flight time for the last 90 days from the last date in added in column A. How can I do it?

 

[AlanY]

try

=SUMPRODUCT((A:A<=MAX(A:A))*(A:A>=(MAX(A:A)-90)),G:G)

 

[Green1972]

Thanks for that. I have tried it and it keep returning a value of 00.33 in each cell I put it in. Also I look back at my original post and realised I forgot to add that the cells in colume G and K a formatted with custom formatting hh;mm as each flight would be minutes long but would add upto hours and minutes and the row 1 are the colume header; Date/Time, Flight duration and total flight duration last 90days. Sorry. etc

 

[AlanY]

here are some dummy data, K1 is formatted as numbers (i.e. in days). need to x24 to hours or x1440 to mins

Book1
	A	G	K
1	08/02/2019	09:07	1.19
2	09/09/2018	10:04
3	13/12/2018	10:48
4	24/08/2018	06:28
5	06/08/2018	11:45
6	21/11/2018	08:38
7	05/07/2018	11:02
8	08/08/2018	11:45
9	26/05/2018	06:28
10	30/10/2018	07:12
Sheet1

Cell Formulas
Range	Formula
K1	=SUMPRODUCT(($A:$A<=MAX($A:$A))*($A:$A>=(MAX($A:$A)-90)),G:G)

 

[Green1972]

Sorry it took me a while to reply. Iam a little confused in your example G1 is 9 min 7

seconds​

(assuming that is what 09:07 means) but in K1 you have 1.19. Multiply 1.19 by 24 you get 28.56, not 9.07? Not sure if iam missing something or not understanding your explanation properly, but the answer still make on sense. also it needs work on a rolling basis so as new date are added any that are over 90 days old need to be ignored

 

[AlanY]

Sorry it took me a while to reply. Iam a little confused in your example G1 is 9 min 7

seconds​

(assuming that is what 09:07 means) but in K1 you have 1.19. Multiply 1.19 by 24 you get 28.56, not 9.07? Not sure if iam missing something or not understanding your explanation properly, but the answer still make on sense. also it needs work on a rolling basis so as new date are added any that are over 90 days old need to be ignored

Col B is formatted as hh:mm,

so, the 1.19 day in K1 is the summation of cells less than 90 days from 2/8/2019 equal to 28hrs 33mins (1.19days)

 

[Green1972]

Ok thanks for clearing that up for me. I got it working except it delivers the same figure in each cell I put it in. It does change as I add new date but it changes it in all the cells prior to that date.

 

[AlanY]

could you post the data/formula you are having problem here so that we can have a look

 

[FormR]

it delivers the same figure in each cell I put it in.

Hi, do you want the formula to calculate the sum based on the date column in A on the row the formula is entered - 90 days?

If so, and assuming your data starts on row 2 - you could try this in K2 copied down.

=SUMIFS(G:G,A:A,">="&A2-90)

=SUMPRODUCT(([COLOR=#ff0000]A:A<=MAX(A:A))[/COLOR]*(A:A>=(MAX(A:A)-90)),G:G)

Hi Alan, isn't the red part redundant? i.e. it has to always be true.

 

[Green1972]

Hi Tried the new formula and getting similar problem. How do I put a screen capture on her to show you, or how can I get the data to you.