Difference SUM(IF(......and IF(SUM(

Question

I had been reading MrExcels book and had come across this formula which he suggested that you use it in regards to positive values.

i also eneterd the formula this way =IF(D6:D10>0,SUM(D6:D10),"") and had the same result, however am sure of the concequences my formula could bring. so am just trying to find out why he suggested that the SUM( function be taken outside the IF( function.

Answer 1

Hi,

To sum all numbers greater then 0, just enter this

=SUMIF($B$3:$B$7,">0",$B$3:$B$7)

Hope this helps.

Answer 2

I had been reading MrExcels book and had come across this formula which he suggested that you use it in regards to positive values.

i also eneterd the formula this way =IF(D6:D10>0,SUM(D6:D10),"") and had the same result, however am sure of the concequences my formula could bring. so am just trying to find out why he suggested that the SUM( function be taken outside the IF( function.

Thanks a lot JLetham 

this is where it started.

{=IF(AND(B3:B7 > 0), SUM(B3:B7),"")}

With this i had the total not calculated untill a value was enterd in the empty cell amongst the range.

then he made this statement: "you might want to sum only those values that are positive. To this you need to move the operation outside the IF() function. e.g. here's an array formula that sums only the value in the range B3:B7 that contain positive values"

{=SUM(IF(B3:B7 >0, B3:B7,0))}

then i also entered this  =IF(D6:D10>0,SUM(D6:D10),"")

NOTE: in the data B6 is empty.

Answer 3

They're actually not quite the same formula.

Enter 5, 5 and -5 in A1:A3 and

{=IF(A1:A3>0,SUM(A1:A3),"")} results in 5

{=SUM(IF(A1:A3>0,A1:A3,""))} results in 10

Answer 4

Consider using:

=SUMPRODUCT(--(D6:D10>0)*(D6:D10))

to avoid using array formulas

Answer 5

Sometimes it's a matter of personal preference or "style"?  I personally think that the way you've written it is probably more understandable, although you didn't show us all of Bill's formula.