A strange sum if formula needed !

[gaftalik]

Hi All ,

I need to sum these cells if a condition doesnt return an error if it returns an error to consider it zero or any other sumif formula to apply it on a larger range :

In C24 i have =C6/C5+C10/C9+C14/C13+C18/C17+C22/C21 which returned error cause some cells contained 0 values .

The formula should sound like if C6/C5 is not an error add it to C10/C9 if this later also is not an error !!!

Thank you much .

Odorizant program zilnic (Air freshner day operation Program).xls
	B	C	D	E
3		09:00
4		11:00
5		10
6		120,17
7		11:00
8		20:00
9		9
10		540,75
11
12
13
14		0,00
15		20:00
16		22:00
17		10
18		120,17
19
20
21
22		0,00
23
24		#DIV/0!
Sheet1

 

[texasalynn]

how about this
=IF(ISERR(C6/C5),0,C6/C5)+IF(ISERR(C10/C9),0,C10/C9)+IF(ISERR(C14/C13),0,C14/C13)+IF(ISERR(C18/C17),0,C18/C17)+IF(ISERR(C22/C21),0,C22/C21)

 

[gaftalik]

Thats great , could it be applied automatically on a larger range of cells or shall i write the formula manually and accordingly ?


Many thanks .

 

[Seti]

Slightly shorter code:

=IF(C5,C6/C5,0)+IF(C9,C10/C9,0)+IF(C13,C14/C13,0)+IF(C17,C18/C17,0)+IF(C21,C22/C21,0)

 

[gaftalik]

it is also a good solution but in case i have a longer range of cells shall i edit all manually ? or there is a trick to work that out instead !

 

[Aladin Akyurek]

If the every 3rd cell regularity the formula below supposes holds...

=SUM(IF(MOD(ROW(C3:C22)-ROW(C3)+1,4)=0,C3:C22,0)/IF((MOD(ROW(C2:C21)-ROW(C2)+1,4)=0)*(C2:C21>0),C2:C21,1))

which must be confirmed with control+shift+enter, not just with enter.

 

[gaftalik]

That's Genious !

Thank you , that's what exactly i was looking for .


Thank you everyone .