Summing with multiple criteria

[qflyer]

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

 

[Biff]

Hi!

Try this:

=SUMPRODUCT(--(A2:A100>=TODAY()-90),--(L2:L100>0),G2:G100)

Adjust ranges to suit.

Note: Sumproduct will not accept whole columns as range arguments: A:A, L:L,
G:G

Biff

 

[qflyer]

That works just fine...while I was messing around with it myself,
found that =SUM(IF((A50:A3000>TODAY()-90)*(L50:L3000>0),H50:H3000)
also works...any ideas on which formula works "best"? They both fin
the same answer, but I will be using various forms of the formula i
over 70 cells which are updated constantly when I add in a new flight.
I found that th
=SUM(IF((A50:A3000>TODAY()-90)*(L50:L3000>0),H50:H3000)) formula cause
a slight slow down (recalculating the sheet takes about .5 seconds
before adding in the new formulas it was immediate) when entering a ne
flight

 

[Biff]

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