=Sum = 0 if no values

[burrelly]

I have a text box that counts the number of days from a selection. The problem is if there are no values in the range the box remains blank.

What I want if there are no values to sum it will = 0

I have tried the following but it does not seem to work:

=Sum(NZ([No of Days],0))

 

[Uncle Gizmo]

Try:
IIf([No of Days] > 0,[No of Days], 0)

 

[burrelly]

Cheers worked perfectly.

 

[burrelly]

Actually no it is not working. It seems to be only counting one of the values. For example one of the records has 19 and 0.5, 0.5 was the last one entered and its only including that in the count.

When it was =sum it would show 19.5.

Anything else I can try?

 

[Uncle Gizmo]

maybe the result is 0.5 out because of a format issue?

 

[burrelly]

I dont think so as simply changing the formula back to =sum it calculates correctly and shows the 0.5

 

[Uncle Gizmo]

You could try this....

Try:
IIf(Sum([No of Days]) > 0,Sum([No of Days]), 0)

 

[burrelly]

That works and calculates correctly

 

[Brianwarnock]

The NZ didn't work because it was in the wrong place
=NZ(Sum([No of days]),0)

Brian