sumif
Posted by zingfree on November 15, 2001 10:44 PM
Hi.
Here's the problem.
I'm trying to write a flying logbook worksheet. What i need to do is have a running 28 day total of my flying hours. I simply cant figure out any particular formula that will do the trick.
A bit of info: My worksheet has multiple entries per column for each days flights. ie, column A might contain three entries for three seperate flights.
I tried converting the dates to serial numbers and then subtracting 28 from it etc, but I cant work it out.
i also tried a sumif function, but i dont know how to include two conditions,
this is the formula i ended up with from the conditional sum wizard, but it doesnt seem to work
here, column AE contains the dates as serial, AF contains the time for each entry (in minutes).
=SUM(IF($AE$6:$AE$30>="AE20-28",IF($AE$6:$AE$30<="AE20",$AF$6:$AF$30,0),0))
formula just gives me zero
!!!!
Any help?
Thanx zing
Posted by George on November 16, 2001 2:45 AM
I'll try to have a quick look at this, but for now go into the formula you have entered above and make it an array (Hold Ctrl, Shift and press Enter). See if this fixes it.
George
Posted by George on November 16, 2001 4:09 AM
Sorry this took so long
Leave the dates in the format that looks easiest to understand.
enter the formula:
=SUM(IF(AE4:AE8>=TODAY()-28,AF4:AF8))
This will give either all totals, or 0
NOW go back into the formula, at the end press CTRL SHIFT & ENTER and this array should have you sorted.
AE4:AE8 can be changed to AE4:AE8000 or as many rows as you may enter into the spreadsheet. Just make sure that AF4:AF8 gets changed to the same figures.
Any queries, just post again
George
