Q:

Error using Sumproduct when one of the ranges contains text and numeric values

I am trying to use SUMPRODUCT function the summarise the data in the below table (Import). I am also using a control sheet, which allows the user to select the 'Handler' whose information they want displayed. The Handler field on the control sheet is defined as 'Controls_Handler'.
DateOfAudit    Handler    ConTotalNumeric
01/06/2015     Dave       0.69
17/07/2015     Dave       N/A
05/06/2015     Dave       0.53
09/06/2015     Steve      0.98

I want to show the average ConTotalNumeric for a selected Handler but the problem I am encountering is that the 'ConTotalNumeric' column contains numeric and text values (the only text value the field can take is 'N/A').
The table in which I am attempting to summarise the data has dates in row 10.
The formula I thought would work is (note: I have had to use the format Table[range]:[range] to make the column references absolute):
=SUMPRODUCT(--(TEXT(Import[[DateOfAudit]:[DateOfAudit]],"mmm 'yy")=TEXT(O$10,"mmm 'yy")), --(Import[[Handler]:[Handler]]=Controls_Handler), --(Import[[ConTotalNumeric]:[ConTotalNumeric]]<>"N/A"), (Import[[ConTotalNumeric]:[ConTotalNumeric]]))/SUMPRODUCT(--(TEXT(Import[[DateOfAudit]:[DateOfAudit]],"mmm 'yy")=TEXT(O$10,"mmm 'yy")), --(Import[[Handler]:[Handler]]=Controls_Handler), --(Import[[ConTotalNumeric]:[ConTotalNumeric]]<>"N/A"))

The idea being that the portion of the formula before the division operator should return the sum of all the ConTotalText range and the portion after the division operator should return a count of all the claims where ConTotalText is numeric; however, the value this returns is zero.
I have separated the two parts of the formula and it appears that the second half works but the first half does not; it returns a zero value.
I have tried replacing the commas with asterisks and have also tried using ISNUMBER() instead of the <>"N/A" comparison but without success - at best I end up with a #value! error.
N.B. the main data table is imported from a database and, although I could write a macro to replace 'N/A' with "" or 0 on workbook open, I do not want to alter the imported data because this would cause issues elsewhere in my workbook.

A:

I've just realised that when the database is imported into Excel, the numeric values in the ConTotalText column are imported as text. Rookie mistake!
All I needed to do was add a few lines to the end macro that I use to refresh the data in the workbook:
Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="<", FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True

These lines covert text to columns using < as a delimiter. As this character doesn't appear in column, the text remains in its original column but coverts to numeric values - where possible.

