Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Summing with multiple criteria
I have a pilot logbook in a spreadsheet. Column A is the date of every flight flown in the past 5 years. Column G is the number of landings made on each flight. If the flight was at night, the number of hours (such as 1.2 or 3.6) is entered in column L. If the value in L is 0, it is assumed the flight was in the day time. To remain current, I have to make at least 3 landings at night. So I need a formula that would look at each flight in the past 90 days (Column A), look to see if the flight was at night (Column L), and then if the date is within the past 90 days, and the night hours column is greater than 0, it should total the number of landings (Column G). Can SUMIF look at multiple ranges or do I need a different formula? Hope this is clear enough... Thanks in advance, Scott -- qflyer ------------------------------------------------------------------------ qflyer's Profile: http://www.excelforum.com/member.php...o&userid=24448 View this thread: http://www.excelforum.com/showthread...hreadid=380762 |
#2
|
|||
|
|||
Hi!
Try this: =SUMPRODUCT(--(A2:A100=TODAY()-90),--(L2:L1000),G2:G100) Adjust ranges to suit. Note: Sumproduct will not accept whole columns as range arguments: A:A, L:L, G:G Biff "qflyer" wrote in message ... I have a pilot logbook in a spreadsheet. Column A is the date of every flight flown in the past 5 years. Column G is the number of landings made on each flight. If the flight was at night, the number of hours (such as 1.2 or 3.6) is entered in column L. If the value in L is 0, it is assumed the flight was in the day time. To remain current, I have to make at least 3 landings at night. So I need a formula that would look at each flight in the past 90 days (Column A), look to see if the flight was at night (Column L), and then if the date is within the past 90 days, and the night hours column is greater than 0, it should total the number of landings (Column G). Can SUMIF look at multiple ranges or do I need a different formula? Hope this is clear enough... Thanks in advance, Scott -- qflyer ------------------------------------------------------------------------ qflyer's Profile: http://www.excelforum.com/member.php...o&userid=24448 View this thread: http://www.excelforum.com/showthread...hreadid=380762 |
#3
|
|||
|
|||
That works just fine...while I was messing around with it myself, I found that =SUM(IF((A50:A3000TODAY()-90)*(L50:L30000),H50:H3000)) also works...any ideas on which formula works "best"? They both find the same answer, but I will be using various forms of the formula in over 70 cells which are updated constantly when I add in a new flight. I found that the =SUM(IF((A50:A3000TODAY()-90)*(L50:L30000),H50:H3000)) formula causes a slight slow down (recalculating the sheet takes about .5 seconds, before adding in the new formulas it was immediate) when entering a new flight. -- qflyer ------------------------------------------------------------------------ qflyer's Profile: http://www.excelforum.com/member.php...o&userid=24448 View this thread: http://www.excelforum.com/showthread...hreadid=380762 |
#4
|
|||
|
|||
Hi!
The =Sum(IF.....) version is an array formula and in most cases array formulas take more resources (time to calc, disk space that translates into larger file size and memory useage) than non-array formulas. What makes Sumproduct such a robust function is that it accepts arrays as arguments and in most cases, is more efficient than an array formula. I guess the general rule of thumb is if you can avoid using an array formula, do so. Biff "qflyer" wrote in message ... That works just fine...while I was messing around with it myself, I found that =SUM(IF((A50:A3000TODAY()-90)*(L50:L30000),H50:H3000)) also works...any ideas on which formula works "best"? They both find the same answer, but I will be using various forms of the formula in over 70 cells which are updated constantly when I add in a new flight. I found that the =SUM(IF((A50:A3000TODAY()-90)*(L50:L30000),H50:H3000)) formula causes a slight slow down (recalculating the sheet takes about .5 seconds, before adding in the new formulas it was immediate) when entering a new flight. -- qflyer ------------------------------------------------------------------------ qflyer's Profile: http://www.excelforum.com/member.php...o&userid=24448 View this thread: http://www.excelforum.com/showthread...hreadid=380762 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple Criteria (add or subtract) | Excel Discussion (Misc queries) | |||
Multiple Criteria IF Nesting | Excel Worksheet Functions | |||
Can I get the mode, min, and max with multiple criteria? | Excel Discussion (Misc queries) | |||
sumproduct using multiple criteria | Excel Worksheet Functions | |||
Counting "rows", i.e. simultaneous criteria for multiple cells | Excel Worksheet Functions |