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!

 

[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. =)