Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Trying to do:

sum=(c5-c4) (eks: 10000 - 2300 =7700)

That works fine but if c5 does not contain a value Excel displays a negative number based on the value in c4.

Is there an easy way to only sum cells if they have a value?

share|improve this question

4 Answers 4

You could try this hack

=IF(AND(B5>0,B4>0), B5-B4,B4)

This looks at the next cell value and the previous cell value, if one or the other does not exist (or is 0 or less) then it displays the previous cell's value or a 0 , other wise it does the maths.

Another option is to check if the value is less than 0 (not sure if that will work in your situations) and if so, make it a positive value, something like;

=IF(B8-B7>0, B8-B7, (B8-B7)*-1)
share|improve this answer
    
neither would work if you have negative numbers as a valid input –  gt6989b Mar 25 '13 at 15:31

Perhaps if you want a more general solution, which allows negative numbers, you can test for numeric input directly:

IF( AND(ISNUMBER(C6),ISNUMBER(C5)), C6-C5, "")

share|improve this answer

In addition to Dave Rook's & gt6989b's answer, here are a couple of formulas that check if both cells have values before taking the sum. If either cell is empty, the formula doesn't perform the sum.

  • =IF(AND(C4<>"",C5<>""),C5-C4,"")
  • =IF(OR(ISBLANK(C4),ISBLANK(C5)),"",C5-C4)
share|improve this answer

No reason for long comparisons with AND(). You want to run the formula only when both cells contain a value, try

=if(count(c4:c5)=2,c5-c4,"")

The previous suggestions checking if each cell is greater than zero will probably return the same result in most cases. But there is a difference.

For example if one of the cells legitimately contains a 0 or a negative number, then you will not see a number as the result.

Count() however, simply counts how many cells contain numbers. The formula will return the calculation result only there are two numbers in two cells, regardless of their value.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.