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

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

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

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