# A strange sum if formula needed !

#### gaftalik

##### Well-known Member
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
BCDE
309:00
411:00
510
6120,17
711:00
820:00
99
10540,75
11
12
13
140,00
1520:00
1622:00
1710
18120,17
19
20
21
220,00
23
24#DIV/0!
Sheet1

### Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
=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)

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

Many thanks .

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)

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 !

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.

That's Genious !

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

Thank you everyone .

Replies
27
Views
690
Replies
11
Views
371
Replies
1
Views
197
Replies
6
Views
307
Replies
3
Views
203

1,196,409
Messages
6,015,106
Members
441,870
Latest member
kojack

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back