SUMIFS with Date Range and Condition

Provided what is being displayed as 00:42 is really a duration value, and means o houns and 42 minutes, the same formula should work. The difference in display is simply a difference in format choice,

Here's the results table from my example with the Durations formatted to match your choice:

Note that the zeroes in rows 7 through 10 have not been formatted, as the sum defaults to a nnumber value where there have been no values to sum.

(Ignore the 256 in the middle—I was using the table as a scratch pad in connection with a different question.)

Regards,

Barry

DURATION converts a series of numbers into a Duration value. If your values to be summed are already durations, DURATION is unnecessary, and should be dropped.

SUMIFS requires 1 + 2n arguments, where n is the number of conditions that must be met.

=SUMIFS(sum-values,test-values,condition,test-values,condition)

The first argument is the range of cells containing the values to be summed.

The second and third (and each pair after that) are the range of cells containing the test-values, then the condition that those values must meet.

With respect to syntax, your formula, shown here with returns entered to separate the elements, looks correct.

=

SUMIFS(

Andreas::Table 1::L;

Andreas :: Table 1 :: H;">="&$C$2;

Andreas :: Table 1 :: J;"<="&$D$2;

Andreas::Table 1::D;"="&B4

)

(Note: The last condition is revised in my example)

Column L: This column should contain the values to be summed. These should be Duration values.

Column H contains the starting date for (the section of) the project recorded on a row. The condition states this date must be equal to or later than the date in cell C2 of the table containing the formula.

Column J contains the end date for (the section of) the project recorded on a row. The condition states this date must be equal to or later than the date in cell D2 of the table containing the formula.

Column D contains the name of (the section of) the project recorded on a row. The condition states this text must match the text in cell B4 of the table containing the formula.

Here's my sample data table, which contains data only in the columns used in the formula:

Here is the table containing the formula (and the results):

Formula, placed in B2 and filled down

=SUMIFS(Table 1::L,Table 1 :: H,">="&$C$2,Table 1 :: J,"<="&$D$2,Table 1::D,"="&A)

Duration values were set to provide an easy check of how many projects have been summed on each project (one for each of projects one, two, four and five, two for project three, and none for lines six, seven, eight and nine).

Three rows were not summed:

Row 1 (project four): Starting date before January 1, 2013

Row 9 (Project one): End date after January 31, 2013

Roow 10 (Project two): End date after January 31, 2013

Note: As There is only one Table 1 included in the document, the Sheet name (Andreas) was dropped from the formula by Numbers. With "Table 2" renamed as "Table 1, Numbers automatically revised the formula to include th Sheet name:

=SUMIFS(Andreas::Table 1::L,Andreas::Table 1 :: H,">="&$C$2,Andreas::Table 1 :: J,"<="&$D$2,Andreas::Table 1::D,"="&A)

Regards,

Barry

A,

I'd like to suggest a change of layout. I don't like to see your summary data straddling your log data. They should be in separate tables.

Then, I think you should take another look at the examples for SUMIFS. The comparison operators, as you are using them, need to be strings.

For example, E"≤"$H$2), the last condition expression in your expression, is neither a string nor a proper condition.

Once you untangle the data and summary we can help with the SUMIFS details.

Jerry

Oops I have a typo:

=SUMIFS(F,C,A5,D">="$G$2,E"<="$H$2)

should have been:

=SUMIFS(F,C,"="&A5,D, ">="&$G$2,E, "<="&$H$2)

You should still consider rearranging like Jerry suggests.

A,

If you are in a country where the decimal separator is a comma, you will need to have semicolons to separate arguments in the Functions of your expressions.

I notice that you have semicolon delimiters in the Excel example.

Jerry

A,

There is a Duration format and there is a Duration Function. You don't need the Function here, but you do need the Format. The Format is applied from the Format menu or the Cells Inspector.

I suggest that you read Chapter 4 of the Numbers User Guide, downloaded from the help menu. It discusses formatting cells. If you haven't already done so, it would be a good idea to also read Chapter 3, perhaps first.

Jerry

Andreas,

The reason I suggested that you read the chapters on tables and cells is that your expression

=DURATION(SUMIFS(Andreas::Table 1::L;Andreas :: Table 1 :: H;">="&$C$2;Andreas :: Table 1 :: J;"<="&$D$2;Andreas::Table 1::D;"="&B4))

is not a proper one. I thought that you may be confused about the difference between applying a format to a cell and writing a DURATION function in a cell.

It can't hurt to read the guide, but I'm sorry that you didn't find any useful information to apply to your current problem.

Jerry

I suggest using the formula:

=SUMIFS(F,C,A5,D">="$G$2,E"<="$H$2)

the Greater than or equal character is not recognized as an operator. Ditto for Less than or equal