Pilot Logbook...

[andrebragstad]

Hey there. I'm currently working on an excel spreadsheet to effectively log my flight hours. I'm trying to make this spreadsheet calculate just about EVERYTHING for me. It's working out allright, but I'm stuck right now.

Here's the problem. Might be hard to visualize very well, but I'll try.

In coloumn B:4 through B:4999 I have the different makes and models of aircraft that I've flown. (300 CB, Robinson 44, etc...)
In coloumn H:4 through H:4999 I have the total time of each flight. (Using decimals, so an hour and a half is 1,5 hours)

So, now I want Excel to add up hours in the different types of makes and models of aircraft. Still following?

That means, in all of the coloumns between B:4 and B:4999 that have the make and model "300CB", I would want Excel to add up the appropriate times in coloumns H:4 to H:4999. That way ending up with a box telling me how many of my total flight hours I spent in the 300CB...

...Is that at all understandable...? Hm... Hopefully someone out there will know what the hell I'm talking about, and maybe give me an input. =)

Thanks for your time so far!

[Tim Miser]

In cell H5001, enter: =SUBTOTAL(9,H4:H4999)

Then turn on the autofilter and each time you select a plane in colum B, it
will subtotal your flight hours in cell H5001.

Good luck & happy flying!


"andrebragstad" wrote:

>
> Hey there. I'm currently working on an excel spreadsheet to effectively
> log my flight hours. I'm trying to make this spreadsheet calculate just
> about EVERYTHING for me. It's working out allright, but I'm stuck right
> now.
>
> Here's the problem. Might be hard to visualize very well, but I'll
> try.
>
> In coloumn B:4 through B:4999 I have the different makes and models of
> aircraft that I've flown. (300 CB, Robinson 44, etc...)
> In coloumn H:4 through H:4999 I have the total time of each flight.
> (Using decimals, so an hour and a half is 1,5 hours)
>
> So, now I want Excel to add up hours in the different types of makes
> and models of aircraft. Still following?
>
> That means, in all of the coloumns between B:4 and B:4999 that have the
> make and model "300CB", I would want Excel to add up the appropriate
> times in coloumns H:4 to H:4999. That way ending up with a box telling
> me how many of my total flight hours I spent in the 300CB...
>
> ...Is that at all understandable...? Hm... Hopefully someone out there
> will know what the hell I'm talking about, and maybe give me an input.
> =)
>
> Thanks for your time so far!
>
>
> --
> andrebragstad
> ------------------------------------------------------------------------
> andrebragstad's Profile: http://www.excelforum.com/member.php...o&userid=32235
> View this thread: http://www.excelforum.com/showthread...hreadid=519917
>
>

[Pete_UK]

Another way is to set up a list of all the types of aircraft in another
column - suppose this occupies M4 to M50. In cell N4 you can enter this
formula:

=SUMIF(B$4:B$4999,M4,H$4:H$4999)

and copy down to cell N50. You will then have the total hours against
each aircraft type. Note that the entries in M4:M50 and in B4:B4999
will have to match exactly - in your post you referred to both "300CB"
and "300 CB", so if your data varies like this then you won't count all
the hours.

Hope this helps.

Pete

[Flintstone]

Hello

You could create a grid using IF formulas, somrthing like this.

=IF(B4="300 CB",H4,"") column J
=IF(B4="Robinson 44",H4,"") column K
=IF(B4="Skull Slapper 200",H4,"") column L

The first formula would go in the range J4:J4999 or whatever column you choose, then =SUM(J4:J4999), placed in whatever slot you choose. The next make and model would be reflected in column K and so on, until all models are represented by their own column.

Matt

[andrebragstad]

I appreciate the feedback! Thank you guys. This helps a lot. =)