add hours flown last 12 months

[haanappel]

i'd like to add the total hours flown the last 12 months(sheet: totals)
counting from the last entry (sheet: logbook). See attachement..

Attachment filename: logbook.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=62816

 

[Frank Kabel]

Hi
not really sure which column you want to sum (looking briefly at your
attachment). You may try to explain your problem in plain text (most
people won't open an attachment)

 

[Ron Rosenfeld]

i'd like to add the total hours flown the last 12 months(sheet: totals),
counting from the last entry (sheet: logbook). See attachement...

Attachment filename: logbook.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=628163

Not sure exactly what you want. But, for example, your last six months of PIC
time would be:

=SUMIF($A$4:A95,">"&DATE(YEAR(A95),MONTH(A95)-6,DAY(A95)),$O$4:O95)

referring to sheet:Logbook


--ron

 

[haanappel]

Ron, your suggestion doesn't seem to work. I want to add all the Pi
,Cop and the Dual columns, so a total of 10 columns (see shee
Logbook). This then will be the total time I have flown. But I want i
to be for a period of 12 months, counting from the last flight (th
last entry). So if I add another flight at the bottom, the total hour
flown last month (see sheet totals) changes accordingly. I appreciat
your help, this one is a real brainer... The attachment is a differen
one than before. I had to delete a lot of data to keep the file withi
the forum rules

Attachment filename: logbook.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=63198

 

[Ron Rosenfeld]

Ron, your suggestion doesn't seem to work. I want to add all the PiC
,Cop and the Dual columns, so a total of 10 columns (see sheet
Logbook). This then will be the total time I have flown. But I want it
to be for a period of 12 months, counting from the last flight (the
last entry). So if I add another flight at the bottom, the total hours
flown last month (see sheet totals) changes accordingly. I appreciate
your help, this one is a real brainer... The attachment is a different
one than before. I had to delete a lot of data to keep the file within
the forum rules.

Attachment filename: logbook.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=631983

Well, to be more precise, the formula for the last twelve months, referenced to
the latest date in your logbook, for dual engine PIC time would be:

=SUMIF(A:A,">="&DATE(YEAR(MAX(A:A))-1,MONTH(MAX(A:A)),DAY(MAX(A:A))),G:G)

You'll have to ensure that none of your header dates in column A have a value
in column G, but that's a matter of layout. As near as I can tell, if you
don't have a date value in a Totals row, you should be OK.

To use the formula on your totals sheet, merely prefix each cell reference
range with Logbook!.

=SUMIF(Logbook!A:A,">="&DATE(YEAR(MAX(Logbook!A:A))-1,MONTH(MAX(Logbook!A:A)),DAY(MAX(Logbook!A:A))),Logbook!G:G)

Then, since you want to have more in that total range than just the PIC dual
engine time, add further SUMIF functions, changing the sum_range. For example,
to include COP dual engine time:

=SUMIF(Logbook!A:A,">="&DATE(YEAR(MAX(Logbook!A:A))-1,MONTH(MAX(Logbook!A:A)),DAY(MAX(Logbook!A:A))),Logbook!G:G)+
SUMIF(Logbook!A:A,">="&DATE(YEAR(MAX(Logbook!A:A))-1,MONTH(MAX(Logbook!A:A)),DAY(MAX(Logbook!A:A))),Logbook!H:H)

and so on until you have all the totals in which you are interested.

The SUMIF function runs faster than array functions or sumproduct, and also can
reference an entire column.


--ron

 

[haanappel]

Ron,

Thank you for your reply. I receive an error message though, when
enter the formula. Why I'm not sure. Trying to solve that now.

Regards,

Fran

 

[Ron Rosenfeld]

Ron,

Thank you for your reply. I receive an error message though, when I
enter the formula. Why I'm not sure. Trying to solve that now.

Regards,

Frank

Could it be line wrap issue?


--ron