# Data Type Calculation Errors

#### thorburnc

##### New Member
I have a workbook that calculates a sum of numbers for each week requested (a reporting tool) from 7-8 worksheets within this workbook.

My problem is that sometimes a cell does not contain a number so it is blank and when the formula looks at it to add the value to the sum, it is reading it as a different data type and can't add the "0" value to the ongoing sum calculation.

I hope that makes sense...we keep getting a debug error because it can't sum up all the numbers.

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

#### DonkeyOte

##### MrExcel MVP

you could possibly create an array or just use a handler:

=IF(ISNUMBER(cell1),cell1,0)+IF(ISNUMBER(cell2),cell2,0)

#### thorburnc

##### New Member
Well, it happens in two spots.

The first formula that I get this error is:

If c.Interior.ColorIndex = xlColorIndexNone Then dbl = dbl + c.Value

Then when I do the reporting for each of the sheets, the formula is

s = s + wksht.Cells(c + B, T).Value

#### DonkeyOte

##### MrExcel MVP
Same theory applies in VBA, you should test the variable before trying to add it, ie

Rich (BB code):
``If c.Interior.ColorIndex = xlColorIndexNone And IsNumeric(c.Value) Then dbl = dbl + c.Value``

Make sense ?

#### Norie

##### Well-known Member
Christine

An empty cell should be no problem when adding.

Care to post the rest of your code?

Perhaps you should be looking at some of the conversion functions like Val, CDbl, CInt etc.

#### thorburnc

##### New Member
Function sumWhite(rng As Range)
Dim c As Range
Dim dbl As Double
For Each c In rng
If c.Interior.ColorIndex = xlColorIndexNone Then dbl = Sum(dbl + c.Value)
Next
sumWhite = dbl
End Function

That is my code...

I will try adding in the ISNumberic(c.value) and hopefully that works! Thanks!

#### thorburnc

##### New Member
Adding the "And IsNumeric" statement worked!

Thank you!

Replies
5
Views
103
Replies
11
Views
166
Replies
1
Views
84
Replies
5
Views
68
Replies
12
Views
194

1,191,687
Messages
5,988,023
Members
440,125
Latest member
vincentchu2369

### 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