Numbers formulae issues

Hi Greg,

It can be challenging to work with dates and times in spreadsheets. And, Numbers treats these values differently than Excel does, so any background in Excel can work against you.

Here's my first suggestion: Take the 30-day math out of the formula and put it in a separate table/cell. Then, just refer to that cell. NOW() - 30 will be the same for every reference in the document, so it doesn't need to appear on every line. Further, I would use TODAY rather than NOW unless you are splitting hairs.

So, you could have a 1-cell table named "Minus30" and refer to it.

Also, if your calculation is in the same table and on the same row as the data you are referring to, you don't need the Sheet and Table names in the reference. Then your expression would simplify to:

=IF(A < Minus30 :: A1, SUM(Table 1 :: K4:P14),0)

Or something close to that. I haven't actually tested, but I will if you think we're going in the proper direction. I'm a bit confused as to where this expression resides.

Jerry

Hi Greg,

You're looking to SUM the values in six columns that are associated with dates in Column A which are on or after the date 30 days ago. For your example set, the sum is 1.1 hours, as only the flight on July 25 this year fits the conditions.

Syntax for SUMIF is: =SUMIF(test-values, condition, sum-values)

Test values are the values in Log::A (all values in non-header rows in this column—rows 4 - 16 in my example)

Condition: the value must be later (greater than) or on (equal to) the date in Last 30 Days::A2. The condition must be expressed as a text string.

Sum values: The range containing the sum values must be the same size as the range containing the test values. Since you have only one column of test values, you will need one iteration of SUMIF for each column of flying hour values.

Here's the SUMIF for column M, the only column containing flying hour values in the sample data:

=SUMIF(Log :: A,">="&$A$2,Log :: M)

The full formula, placed in B2 of the small table, uses SUM and six iterations of the SUMIF above:

Last 30 Days::B2:

=SUM(SUMIF(Log :: A,">="&$A$2,Log :: K),SUMIF(Log :: A,">="&$A$2,Log :: L),SUMIF(Log :: A,">="&$A$2,Log :: M),SUMIF(Log :: A,">="&$A$2,Log :: N),SUMIF(Log :: A,">="&$A$2,Log :: O),SUMIF(Log :: A,">="&$A$2,Log :: P))

The starting date for the 30 day period, shown in A2, uses this formula:

Last 30 Days:A2: =TODAY()-30

Regards,

Barry