SUMIF Alternative?
 

Hey folks,

I'm a pilot and to remain "current" I must fly 6 instrument approaches
within the past 180 days. I have my logbook on a spreadsheet and column
A lists the dates of every flight, and column F lists any instrument
approaches flown on each flight. I'd like to sum the values in column F
if the date is greater than TODAY()-180. The formula would look like
=SUMIF(A50:A3000,"TODAY()-180",F50:F3000).

The SUMIF formula won't compute anything with a TODAY() in it, so I
have to manually enter in the date I'd like to look after
(=SUMIF(A50:A3000,"12/20/2004",F50:F3000)). How can I SUM the number
of approaches flown within the past 180 days without using a SUMIF?

Thanks a lot,
Scott


--
qflyer
------------------------------------------------------------------------
qflyer's Profile: http://www.excelforum.com/member.php...o&userid=24448
View this thread: http://www.excelforum.com/showthread...hreadid=380457

See response to your other post w/same question.


"qflyer" wrote:


Hey folks,

I'm a pilot and to remain "current" I must fly 6 instrument approaches
within the past 180 days. I have my logbook on a spreadsheet and column
A lists the dates of every flight, and column F lists any instrument
approaches flown on each flight. I'd like to sum the values in column F
if the date is greater than TODAY()-180. The formula would look like
=SUMIF(A50:A3000,"TODAY()-180",F50:F3000).

The SUMIF formula won't compute anything with a TODAY() in it, so I
have to manually enter in the date I'd like to look after
(=SUMIF(A50:A3000,"12/20/2004",F50:F3000)). How can I SUM the number
of approaches flown within the past 180 days without using a SUMIF?

Thanks a lot,
Scott


--
qflyer
------------------------------------------------------------------------
qflyer's Profile: http://www.excelforum.com/member.php...o&userid=24448
View this thread: http://www.excelforum.com/showthread...hreadid=380457

qflyer wrote:
Hey folks,

I'm a pilot and to remain "current" I must fly 6 instrument approaches
within the past 180 days. I have my logbook on a spreadsheet and column
A lists the dates of every flight, and column F lists any instrument
approaches flown on each flight. I'd like to sum the values in column F
if the date is greater than TODAY()-180. The formula would look like
=SUMIF(A50:A3000,"TODAY()-180",F50:F3000).

The SUMIF formula won't compute anything with a TODAY() in it, so I
have to manually enter in the date I'd like to look after
(=SUMIF(A50:A3000,"12/20/2004",F50:F3000)). How can I SUM the number
of approaches flown within the past 180 days without using a SUMIF?

Thanks a lot,
Scott



=SUMIF(A50:A3000,"="&TODAY()-180,F50:F3000)

assuming that there will never be any date in A that is later than
today's date.

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.