Microsoft Excel - Numbers won't sum

I am using a VBA to query and fill a query table with data from another excel workbook.  I am having problems with a field name Hours which is defined as a number with 2 decimal places in both workbooks.  When I use the sum function on that particular column it always sums at 0.00.  It is as if the cells are being seen as text instead of numbers.  Any ideas?

This is a piece of my code:

 bdate = Range("begin").Value
   edate = Range("end").Value
   fdate = 1 / 1 / 2009
   Sheets("Calc SP").Select
    Application.Goto Reference:="CalcSPClearArea"
    Selection.ClearContents
   wherestatus = " AND ((`Query_from_fipe109#ffdc`.STATUS IN ('COMPLETE', 'AWAIT ADDS') AND `Query_from_fipe109#ffdc`.STATUS_DATE Between #" & bdate & "#" & " And #" & edate & "#)"
   orstatus = " OR (`Query_from_fipe109#ffdc`.STATUS IN ('IN PROCESS') AND `Query_from_fipe109#ffdc`.STATUS_DATE Between #" & fdate & "#" & " And #" & edate & "#));"
   wherejobtype = " where `Query_from_fipe109#ffdc`.JOB_TYPE = 17"
   Set cnn = New ADODB.Connection
   Set cmd = New ADODB.Command
   Set rs = New ADODB.Recordset
   strDbPath = "I:\Map_Folders\NZ170Y\Metrics\Reporting\CurrentData.xlsx"
   cnn.ConnectionString = "Provider=Microsoft.Ace.OLEDB.12.0;" & "Data Source=" & strDbPath & ";" & "Extended Properties=""Excel 12.0 Xml;HDR=Yes;"""
   cnn.Open
   Set cmd.ActiveConnection = cnn

   cmd.CommandText = "Select `Query_from_fipe109#ffdc`.JOB_NO AS JOB_NO, `Query_from_fipe109#ffdc`.CLIENT AS CLIENT, `Query_from_fipe109#ffdc`.HOURS_WRKD AS HOURS, `Query_from_fipe109#ffdc`.CLLI AS CLLI,`Query_from_fipe109#ffdc`.OPERATOR_ID AS OPERATOR, `Query_from_fipe109#ffdc`.STATUS AS STATUS   from `I:\Map_Folders\NZ170Y\Metrics\Reporting\CurrentData.xlsx`.`Query_from_fipe109#ffdc`" & wherejobtype & wherestatus & orstatus
   Set rs = cmd.Execute
   Set qt = ActiveSheet.QueryTables.Add(Connection:=rs, Destination:=ActiveSheet.Range("A13"))
   qt.Refresh  

 rs.Close
   cnn.Close
   Set rs = Nothing
   Set cnn = Nothing

That interesting code aside, it sounds as though column C of your destination sheet may be formatted as Text before the query result set gets dropped in. If you turn on the Number stored as Text warning, do these values get flagged?

Do you format the column's cells before or after the query result is dropped in? If after, format them before.

Does the + operator work with the values? I.e. if you use the addition operator on a few of the values rather than the SUM function, is the result still zero? =C14+C15+C16

The column is formated before it is populated.

Oddly enough, =C17+C18+C21+C22+C23+C24 works

=SUM(C17:C24) does not

I cannot use the + operator since I don't know how many rows I will have in the column when it is populated.

This sounds like the same behavior I mentioned the other day on this thread:

http://www.eggheadcafe.com/community/aspnet/66/10125942/re-sum-values-with-symbo.aspx

My guess is that the values have some characteristic (embedded spaces?) that cause them to be non-numeric. I recommend that you experiment with the values to discover and fix this characteristic of the query values (perhaps try adding IMEX=1 or IMEX=0 to the extended properties of the connection string). Alternatively you could force the values to numeric after they're created, or use a solution like I posted in that thread.

Using your suggestions I cannot find anything that works. 

 I did discover something interesting. 

When I select one of the cells in the column and then click on the formula bar where the value is displayed

 then click out of it, the value becomes a number.  

Not sure why or how I can use this to change the data type for the entire range.  Have any ideas?

Yes, you are causing the text values to be reevaluated as numbers. You can do that by double-clicking the cells or using F2 (edit) Enter.

If you cannot get the query results to be numeric, then you can change them en masse after they are dropped in by selecting the range and choosing Text to Columns (on the Data ribbon) and then clicking Next, Next, Finish on the dialog boxes.