Formulas and Conditional Formatting

[Dooha]

I seem to have some how decided to produce the most complicated spreadsheet in the world (in my head anyway)

I have a formula -

=SUMIF(Flying!B7:B322,">"&EDATE(TODAY(),-9),Flying!L7:L322)

This tells me all the flying I have done in the last 9 months on a separate worksheet.

I have another column O7-O322 which is a remarks column detailing which exercise was carried out on each flight. Example O8 has 'Exercise 17', O9 has 'Exercise 18' written in it. I need to recognise only exercise 18 in column 'O', but for the past 9 months only. Is there anyway I can incorporate the formula above to recognise 'Exercise 18' in column O7-O322, or better still recognising Exercise 18 even if Exercise 18,12, and 19 were written in the same cell.

A slightly easier question -

Im trying to conditionally format a cell where the number turns Green if over 25:00 hrs. I can make it turn Green if the number 25 was in the cell, but when it's in the hours format 25:00, it doesnt recognise it. Not sure how to get around this.

 

[T. Valko]

I think we are getting somewhere. I have created the named formula, and inserted the other formula. The formula seems work as it diplays as 'True', and 'False' if it is older than 9 months.

So need the formula to add all the hours from L7:L322, that are older than 9 months (taken from B7:B322), and have 'Exercise 18' (taken from O7:O322)

Because of the amount of flying and data involved for all the student pilots, I need this kinda spreadsheet to take the load off and the amount of time and inaccuracies.

Ok, the formula to get the sum of hours would be...

=SUMPRODUCT(--(Flying!B7:B322>EDATE(NOW(),-9)),--(ISNUMBER(SEARCH("Exercise 18",Flying!O7:O322))),Flying!L7:L322)