PPRuNe Forums > Misc. Forums > Computer/Internet Issues & Troubleshooting > Excel logbook question PDA View Full Version : Excel logbook question Squawk777716th Apr 2012, 04:18Any Excel gifted ppruners that can help me out with the following issue? I have my logbook in Excel and I am currently flying three different aircraft types, that I like to break in hours flown in each type. I have a cell that I dedicated "aircraft type". What formula do I need to use if I want to specify e.g. "if aircraft type" cell = E190 than add time in cell "YY" to cell "xx"? Any suggestions? 7 7 7 7 ExGrunt16th Apr 2012, 06:517777, Assume that you have the type in colum B and the corresponding hours in column C and there are 10 rows of data from row 2 - 11. Then you can use the SUMPRODUCT function to calculate the numbers for each type: Put the type name in cell A12 Put this formula in A13: =SUMPRODUCT((B2:B11=A12)*C2:C11) HTH EG piperarcher17th Apr 2012, 11:25Or try this =COUNTIF(B:B,"=Type") You can extend it to multiple counts in one cell e.g. =COUNTIF(B:B,"=Type") + COUNTIF(C:C,"=Othertext") bnt17th Apr 2012, 11:44If you're looking to analyse the data, note that Excel has various features to help you extract various info, without having to write functions. For example, you can sort the records by aircraft type, then use the Subtotals button to automatically insert subtotals of hours by aircraft type. http://media.wiley.com/Lux/92/72492.image0.jpg More details: Inserting Subtotals in an Excel 2007 Worksheet - For Dummies (http://www.dummies.com/how-to/content/inserting-subtotals-in-an-excel-2007-worksheet.html). Genghis the Engineer17th Apr 2012, 11:45I use " =SUMIF(range-containing-aircraft-types,cell-containing-aircraft-name, range-containing-flying-hours) ". My Excel logbook handles that fine for 100ish types. My person preference is to split it over three sheets that contain: (1) Classical logbook information (2) Calculations (3) 1 page presentational logbook summary that I can print out. (1) I enter data on, (3) is all that useful stuff like total experience, hours on type, hours in last 28/90/365 days, instructional hours, expiry dates.... (2) is just necessary but best kept away from the other two. G ExGrunt17th Apr 2012, 12:59Genghis, I agree that your formula is an equally workable solution and your separation of inputs, workings and outputs is well in line with good excel practice. As a counsel of perfection have you considered adding a further worksheet of cross check calculations? eg: count of input data matches count of output data, sum of input hours matches sum of output hours. HTH EG Genghis the Engineer17th Apr 2012, 14:15No, but that's a good idea. A task for a long winter evening. G