Microsoft Office Tutorials and References
In Depth Information
Conditional Sums Using a Single Criterion
Summing only negative values
The following formula returns the sum of the negative values in column F. In other words, it returns
the total number of past-due days for all invoices. For this worksheet, the formula returns –63.
Because you omit the third argument, the second argument (“<0”) applies to the values in the
You can also use the following array formula to sum the negative values in the
You do not need to hard-code the arguments for the SUMIF function into your formula. For
example, you can create a formula such as the following, which gets the criteria argument from
the contents of cell G2:
This formula returns a new result if you change the criteria in cell G2.
Summing values based on a different range
The following formula returns the sum of the past-due invoice amounts (see column C in
This formula uses the values in the Difference range to determine whether the corresponding
values in the Amount range contribute to the sum.
You can also use the following array formula to return the sum of the values in the
Amount range, where the corresponding value in the Difference range is negative: