Sum problem

[Mel Smith]

I am trying to add a series of cells using a simple sum formula. The cells (which are formatted as m:ss) will get values from other cells when they are populated. This means that some cells in my series will be blank. How can i avoid getting the #VALUE! error message and just add, say 10 of the 15 cells that have a value?

Mel

 

[MARK858]

Are your cells actually blank or are they formulas returning ""?

 

[Mel Smith]

Hello Mark, They are returning "" from a formula: =if(I21="","",F21/(60*60*24)) and so on...

Mel

 

[shift-del]

Hello Mel

You are not using a simple SUM() formula but rather adding cells.
Do NOT add cells. Use the SUM() function.
Better: Change your formula to F21/(60*60*24).

 

[Mel Smith]

Hi Mark,

Then i get the cell showing 0:00 which when i later try to average them, it counts the 0:00 too.

Mel

 

[MARK858]

Maybe....

Book1
	A	B			E	F	G	H	I
2	19:20				04:33:34	Sum
3	22:56				15:45	Average
4
5	22:34
6	13:38
7	22:10
8	18:43
9									15:45
10	21:06
11	06:28
12	22:54
13	06:53
14
15	07:38
16	13:23
17
18	18:12
19
20	04:31
Sheet1

Cell Formulas
Range	Formula
E2	E2	=SUMIF(A2:A20,"<>""",A2:A20)
E3	E3	=AVERAGEIF(A2:A20,"<>""",A2:A20)
I9	I9	=AVERAGE(A2,A3,A5,A6,A7,A8,A10,A11,A12,A13,A15,A16,A18,A20)
A2:A20	A2	=IF(C2="","",C2)

Or as @shift-del has stated for the Sum part only you can just use Sum rather than Sumif

 

[Mel Smith]

Hi Mark,

Sorry for the delay. Just the job, thank you!

Mel

 

[MARK858]

You're welcome, happy we could help