Sum Prodcut issue from .txt import

[Chicago Joe]

I've reviewed the many SUMPRODUCT threads here, but none of the
solutions seem to be working. I have been using the same Sumproduct
formula and downloading new data from dvent Axys into excel using a
general import.

However, to make a long story short, we cannot use the excel export
anymore. Now we have to save the report as a .txt file and import it
into excel using the import wizard. I've been importing using fixed
width.

Once the data is pulled into excel, the sumproducts become #VALUE!.
Here is the formula

=SUM(('AXYS DATA'!$M$11:$M$65000="Research")*('AXYS DATA'!$K$11:$K
$65000)*('AXYS DATA'!$I$11:$I$65000=SUMMARY!$B19))


(('AXYS DATA'!$M$11:$M$65000="Research") searches for the word
"Research" in column M. However, after the .txt file is imported a
routine is performed via macro to make the word show exactly as we
want. First, the following formula is put in an adjacent column:
=IF(I11="","",IF(M11="research","Research",IF(M11="Best
Execution","Best Execution",IF(M11="Directed","Directed","Other"))))
Which returns one of the four words we're looking for. The if
statement is then copied and the values pasted in column M.
Therefore, the pasted values of this should return, unless I'm missing
something.

The ('AXYS DATA'!$K$11:$K$65000) is the variable row, as other columns
are substituted to return the different sums. However, when I make
this formula stand alone it does return a total. Therefore, I don't
think this is the problem.

(('AXYS DATA'!$I$11:$I$65000=SUMMARY!$B19)) "B19" is just filled with
a broker code, that changes each row. Column B beside the sumproduct
is pasted in from a filter of column I in the original workbook, so I
know these cells are the same.

If I manually export to excel, this workbook still works correctly.
However, I will need to automate an export to .txt and use the import
wizard. If anyone has any clue why my functions aren't working,
please let me know. Thanks in advance

 

[T. Valko]

Once the data is pulled into excel, the sumproducts become #VALUE!.

So, let's look at your formula:

('AXYS DATA'!$M$11:$M$65000="Research")
('AXYS DATA'!$I$11:$I$65000=SUMMARY!$B19)

Both of those arrays will return either TRUE or FALSE so the problem is not
being caused by either of those arrays.

(array1)*('AXYS DATA'!$K$11:$K$65000)*(array3)

If ('AXYS DATA'!$K$11:$K$65000) is not a number or an empty cell then the
result will be #VALUE!.

You may have leading/trailing spaces like this:

_10_
_10
10_

Where:

(array1)*(_10_)*(array3) = #VALUE!