Help with Conditional Formatting

[Dooha]

Hi,

I have created a spreadsheet displaying all my flying hours. In C9-C39 are all the dates I've flown, and in L9-L39 are the corresponding hours in flight for that date. Now I am only interested in the last 9 months of flying. I have conditionally formatted cells C9-C39 to turn Green if that flight was within the last 9 months. What I would like to do is have a totals box that adds all the flying hours in L9-L39, but only from the last 9 months. Is there a way of doing this? I hope I've explained that clear enough

 

[barry houdini]

Try using SUMIF, e.g. something like

=SUMIF(C9:C39,">"&EDATE(TODAY(),-9),L9:L39)

or less exact....

=SUMIF(C9:C39,">"&TODAY()-274,L9:L39)

 

[Dooha]

Thank you for such a prompt reply. Is that formula to added into a condtional format or as a stand alone formula in the cell. The other cells are a simple =SUM(L9:L39) to get my total flying hours. I have just replaced it with the formula you have given but it doesn't seem to work. I think I may be doing something wrong. I can fly a plane but I am pretty bad at excel. You may have to talk me through it a little.

 

[barry houdini]

That's a worksheet formula to go in a cell.

If you use conditional formatting to format cells with a condition then it is possible to sum using the formatting....but you need VBA. I'd suggest that it's easier to use a formula to generate a sum using the same condition.

SUMIF sums one range based on a condition applied to another range (which is what you want to do). So in that second formula

=SUMIF(C9:C39,">"&TODAY()-274,L9:L39)

That says that L9:L39 should be summed as long as C9:C39 meets the condition......the condition being that the date be larger than today's date minus 274 days (274 being my approximation of 9 months).

Do you get a result with that formula?

The first formula uses EDATE to get the date 9 months before today (which is arguably more accurate) but EDATE may not work for you if you have Excel 2003 or earlier - in those versions it's an "Analysis ToolPak" add-in function and you need to enable that add-in. Do so like this

Tools > add-ins > tick "Analysis ToolPak" box

Note that given today's date (16th July 2011), =EDATE(TODAY(),-9) gives 16th October 2010 and =TODAY()-274 gives 15th October 2010

 

[Dooha]

The formula you have given works perfectly, its just what I wanted. It would seem that you are a master of excel and a credit to this forum.

Thank you, I will be the envy of the other pilots!