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

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" <qflyer.1qyked_1119326709.7677@excelforum-nospam.com> wrote in
message news:qflyer.1qyked_1119326709.7677@excelforum-nospam.com...
>
> 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:A3000>TODAY()-90)*(L50:L3000>0),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:A3000>TODAY()-90)*(L50:L3000>0),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.

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" <qflyer.1qypz4_1119333944.668@excelforum-nospam.com> wrote in
message news:qflyer.1qypz4_1119333944.668@excelforum-nospam.com...
>
> That works just fine...while I was messing around with it myself, I
> found that =SUM(IF((A50:A3000>TODAY()-90)*(L50:L3000>0),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:A3000>TODAY()-90)*(L50:L3000>0),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!

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" <qflyer.1qyked_1119326709.7677@excelforum-nospam.com> wrote in
message news:qflyer.1qyked_1119326709.7677@excelforum-nospam.com...
>
> 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!

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" <qflyer.1qypz4_1119333944.668@excelforum-nospam.com> wrote in
message news:qflyer.1qypz4_1119333944.668@excelforum-nospam.com...
>
> That works just fine...while I was messing around with it myself, I
> found that =SUM(IF((A50:A3000>TODAY()-90)*(L50:L3000>0),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:A3000>TODAY()-90)*(L50:L3000>0),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!

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" <qflyer.1qyked_1119326709.7677@excelforum-nospam.com> wrote in
message news:qflyer.1qyked_1119326709.7677@excelforum-nospam.com...
>
> 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!

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" <qflyer.1qypz4_1119333944.668@excelforum-nospam.com> wrote in
message news:qflyer.1qypz4_1119333944.668@excelforum-nospam.com...
>
> That works just fine...while I was messing around with it myself, I
> found that =SUM(IF((A50:A3000>TODAY()-90)*(L50:L3000>0),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:A3000>TODAY()-90)*(L50:L3000>0),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" <qflyer.1qypz4_1119333944.668@excelforum-nospam.com> wrote in
message news:qflyer.1qypz4_1119333944.668@excelforum-nospam.com...
>
> That works just fine...while I was messing around with it myself, I
> found that =SUM(IF((A50:A3000>TODAY()-90)*(L50:L3000>0),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:A3000>TODAY()-90)*(L50:L3000>0),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!

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" <qflyer.1qyked_1119326709.7677@excelforum-nospam.com> wrote in
message news:qflyer.1qyked_1119326709.7677@excelforum-nospam.com...
>
> 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: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" <qflyer.1qyked_1119326709.7677@excelforum-nospam.com> wrote in
message news:qflyer.1qyked_1119326709.7677@excelforum-nospam.com...
>
> 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!

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" <qflyer.1qypz4_1119333944.668@excelforum-nospam.com> wrote in
message news:qflyer.1qypz4_1119333944.668@excelforum-nospam.com...
>
> That works just fine...while I was messing around with it myself, I
> found that =SUM(IF((A50:A3000>TODAY()-90)*(L50:L3000>0),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:A3000>TODAY()-90)*(L50:L3000>0),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!

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" <qflyer.1qyked_1119326709.7677@excelforum-nospam.com> wrote in
message news:qflyer.1qyked_1119326709.7677@excelforum-nospam.com...
>
> 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!

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" <qflyer.1qypz4_1119333944.668@excelforum-nospam.com> wrote in
message news:qflyer.1qypz4_1119333944.668@excelforum-nospam.com...
>
> That works just fine...while I was messing around with it myself, I
> found that =SUM(IF((A50:A3000>TODAY()-90)*(L50:L3000>0),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:A3000>TODAY()-90)*(L50:L3000>0),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!

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" <qflyer.1qyked_1119326709.7677@excelforum-nospam.com> wrote in
message news:qflyer.1qyked_1119326709.7677@excelforum-nospam.com...
>
> 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!

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" <qflyer.1qypz4_1119333944.668@excelforum-nospam.com> wrote in
message news:qflyer.1qypz4_1119333944.668@excelforum-nospam.com...
>
> That works just fine...while I was messing around with it myself, I
> found that =SUM(IF((A50:A3000>TODAY()-90)*(L50:L3000>0),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:A3000>TODAY()-90)*(L50:L3000>0),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" <qflyer.1qypz4_1119333944.668@excelforum-nospam.com> wrote in
message news:qflyer.1qypz4_1119333944.668@excelforum-nospam.com...
>
> That works just fine...while I was messing around with it myself, I
> found that =SUM(IF((A50:A3000>TODAY()-90)*(L50:L3000>0),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:A3000>TODAY()-90)*(L50:L3000>0),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!

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" <qflyer.1qyked_1119326709.7677@excelforum-nospam.com> wrote in
message news:qflyer.1qyked_1119326709.7677@excelforum-nospam.com...
>
> 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: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" <qflyer.1qyked_1119326709.7677@excelforum-nospam.com> wrote in
message news:qflyer.1qyked_1119326709.7677@excelforum-nospam.com...
>
> 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!

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" <qflyer.1qypz4_1119333944.668@excelforum-nospam.com> wrote in
message news:qflyer.1qypz4_1119333944.668@excelforum-nospam.com...
>
> That works just fine...while I was messing around with it myself, I
> found that =SUM(IF((A50:A3000>TODAY()-90)*(L50:L3000>0),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:A3000>TODAY()-90)*(L50:L3000>0),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!

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" <qflyer.1qyked_1119326709.7677@excelforum-nospam.com> wrote in
message news:qflyer.1qyked_1119326709.7677@excelforum-nospam.com...
>
> 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!

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" <qflyer.1qypz4_1119333944.668@excelforum-nospam.com> wrote in
message news:qflyer.1qypz4_1119333944.668@excelforum-nospam.com...
>
> That works just fine...while I was messing around with it myself, I
> found that =SUM(IF((A50:A3000>TODAY()-90)*(L50:L3000>0),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:A3000>TODAY()-90)*(L50:L3000>0),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!

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" <qflyer.1qyked_1119326709.7677@excelforum-nospam.com> wrote in
message news:qflyer.1qyked_1119326709.7677@excelforum-nospam.com...
>
> 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!

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" <qflyer.1qypz4_1119333944.668@excelforum-nospam.com> wrote in
message news:qflyer.1qypz4_1119333944.668@excelforum-nospam.com...
>
> That works just fine...while I was messing around with it myself, I
> found that =SUM(IF((A50:A3000>TODAY()-90)*(L50:L3000>0),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:A3000>TODAY()-90)*(L50:L3000>0),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
>