1

I am using the formula =SUM('7-10'!F12+'8-10'!J12-'8-10'!F12)

This is to work out average daily sales in 24 hours. But if no sale is recorded for 7-10!f12 but sales are recorded for 8-10!f12 it shows as - (what ever sales there were).

I would like this to show 0 unless there is data in all fields in the formula.

Is this possible?

1 Answer 1

1

You can use a combination of IF and AND to achieve this.

=IF(AND(A1>0,B1>0,C1>0),SUM(A1+B1+C1),0)

See below as to how this works:

Example image 1

Example image 2

2
  • If you ever expect negative values to be in a cell, you can use A1<>0 to basically say; "value must not be 0". Oct 8, 2014 at 2:34
  • Or use =IF(AND(A1<>"",B1<>"",C1<>""), SUM(A1+B1+C1), 0) if one or more of the sales might actually, legitimately be 0 (e.g., if the salesman is out sick, or the state is shut down by a blizzard) but you want to show a result of 0 if the input data are incomplete (i.e., at least one is blank). Oct 8, 2014 at 22:15

You must log in to answer this question.

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