# If cells in a range are zero prevent sumproduct and sum from DIV/0 error

#### DeezNuts

##### Board Regular
How can I fix this formula so if none of the 5 cells in the range have a number value this formula doesnt give an error but displays a - instead. The 5 cells show a - when no value is them. J21 is the formula I am having the problem with

Excel 2013
EFGHIJK
20Grade
21--87.00--87.00A

<tbody>
</tbody>

Worksheet Formulas
CellFormula
E21=IF(COUNT(E3:E20),AVERAGE(E3:E20),"-")
F21=IF(COUNT(F3:F20),AVERAGE(F3:F20),"-")
G21=IF(COUNT(G3:G20),AVERAGE(G3:G20),"-")
H21=IF(COUNT(H3:H20),AVERAGE(H3:H20),"-")
I21=IF(COUNT(I3:I19),AVERAGE(I3:I20),"-")
K21=LOOKUP(J21,AG1:AG18,AH1:AH18)

<tbody>
</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
J21{=SUMPRODUCT(AE3:AE7,AF3:AF7)/SUM(IF(AF3:AF7<>"-",AE3:AE7))}

<tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Last edited:

### Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

#### Aladin Akyurek

##### MrExcel MVP
What is the problem in words, that is, what do you want to calucalate in J21?

#### DeezNuts

##### Board Regular
sorry forgot I had another issue and had moved where this code was getting its data. Here is a mini version of it. The %'s at the top are the weights the blanks between the %'s and the - are where the grades would go

Excel 2013
RSTUVW
2420%22%8%45%5%
25
26
27
28
29
30
31-----#DIV/0!

<tbody>
</tbody>

Worksheet Formulas
CellFormula
R31=IF(COUNT(R25:R30),AVERAGE(R25:R30),"-")
S31=IF(COUNT(S25:S30),AVERAGE(S25:S30),"-")
T31=IF(COUNT(T25:T30),AVERAGE(T25:T30),"-")
U31=IF(COUNT(U25:U30),AVERAGE(U25:U30),"-")
V31=IF(COUNT(V25:V30),AVERAGE(V25:V30),"-")

<tbody>
</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
W31{=SUMPRODUCT(R24:V24,R31:V31)/SUM(IF(R31:V31<>"-",R24:V24))}

<tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

#### Aladin Akyurek

##### MrExcel MVP
In words please, not by means of (non-working) formulas...

Try to post an appropriate sample along with the expected results based on that sample.

#### DeezNuts

##### Board Regular
how can i fix this formula so if none of the 5 cells in the range have a number value this formula doesnt give an error but displays a - instead.
Rich (BB code):
``{=sumproduct(r24:v24,r31:v31)/sum(if(r31:v31<>"-",r24:v24))}``

#### Aladin Akyurek

##### MrExcel MVP
Rich (BB code):
``{=sumproduct(r24:v24,r31:v31)/sum(if(r31:v31<>"-",r24:v24))}``

What do you have in R24:V24 and in R31:V31? Does "-" mean a real 0?

#### DeezNuts

##### Board Regular
Nevermind I used a iferror figured there was better way of doing it. No idea how to explain it any differently.
Code:
``=IFERROR( SUMPRODUCT(E2:I2,E21:I21)/SUM(IF(E21:I21<>"-",E2:I2)),"-")``

#### Aladin Akyurek

##### MrExcel MVP
Nevermind I used a iferror figured there was better way of doing it. No idea how to explain it any differently.
Code:
``=IFERROR( SUMPRODUCT(E2:I2,E21:I21)/SUM(IF(E21:I21<>"-",E2:I2)),"-")``

Use words, not formulas... Not change references continually...

Just enter:

=IFERROR(SUMPRODUCT(E2:I2,E21:I21)/SUMIF(E21:I21,"<>-",E2:I2),"-")

Replies
0
Views
251
Replies
2
Views
349
Replies
7
Views
297
Replies
4
Views
633
Replies
3
Views
204

Threads
1,190,782
Messages
5,982,886
Members
439,803
Latest member
sushilneupane

### 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.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

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

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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