1

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?

4 Answers 4

2

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)
1
  • neither would work if you have negative numbers as a valid input
    – gt6989b
    Mar 25, 2013 at 15:31
2

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, "")

2

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.

0

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)

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .