« Who Needs DATEDIF() ?? Revisited.
Autoformat »

Adding Only Positive Numbers

We can use an array formula to sum up only the positive numbers in a given range.

=SUM(IF(A1:A10>=0;A1:A10;0))

An array formula is defined by hitting SHIFT-CTRL-Enter instead of Enter for a normal (scalar) formula.

In documentation and texts, an array formula is indicated by ‘curly braces’ as shown below

{=SUM(IF(A1:A10>=0;A1:A10;0))}

However, the curly braces should not be included when entering the formula on the spreadsheet.

This entry was posted on Monday, July 30th, 2007 at 9:55 pm and is filed under Function Tips. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

5 Responses to “Adding Only Positive Numbers”

  1. Harlan Grove Says:
    August 1st, 2007 at 9:38 am

    OK, it’s an example, but the better approach would be

    =SUMIF(A1:A10;”>0″)

  2. Lawrence Says:
    September 9th, 2007 at 7:40 pm

    Kindly advise how to construct a formula to find the mimimum POSITIVE value in a range of values. Thank you.

  3. Marc Says:
    November 18th, 2007 at 11:48 am

    I would like to deduct values from two cells..
    How to insert difference function in calc..?

  4. Sly Says:
    November 30th, 2007 at 4:16 am

    Try this, You may say: “too simple to work”, but it does !

    =-sum(A1:A5)

  5. Brin Says:
    December 4th, 2007 at 10:54 am

    Hello, nice site :)

Leave a Reply