I have a worksheet that has many conditional sum functions in it. The array
is exported data out of an accounting program. The formating is the same
between the criteria and the range however I can not seem to get it to work
when I export new data it just comes back with the answer of zero which is
incorrect. I have other worksheets with the same set up except one of the
criteria has letters and numbers in the criteria and they work fine. In the
worksheet that does not work that same criteria is just numbers. Can someone
help me with this issue?

either your criteria is a text value that LOOKS like a number, or the data
from the accounting program is text. Copy an empty cell (Ctrl-C), select all
the supposedly numeric data from the accounting program, and use Edit-Paste
Special- Add You are adding zero, so the numeric values don't change.
However, if Excel thought they were text, this operation forces Excel to
treat them as numbers.

If that doesn't fix your problem, do the same thing to your criteria.


"prongacct07" wrote:

I have a worksheet that has many conditional sum functions in it. The array
is exported data out of an accounting program. The formating is the same
between the criteria and the range however I can not seem to get it to work
when I export new data it just comes back with the answer of zero which is
incorrect. I have other worksheets with the same set up except one of the
criteria has letters and numbers in the criteria and they work fine. In the
worksheet that does not work that same criteria is just numbers. Can someone
help me with this issue?

Thanks for the help. The criteria and the range was already formated as text
manually. the sum column however is still in number format. I have gone
back a reformatted the range and criteria to both be numbers and it still
does not work.

"Duke Carey" wrote:

either your criteria is a text value that LOOKS like a number, or the data
from the accounting program is text. Copy an empty cell (Ctrl-C), select all
the supposedly numeric data from the accounting program, and use Edit-Paste
Special- Add You are adding zero, so the numeric values don't change.
However, if Excel thought they were text, this operation forces Excel to
treat them as numbers.

If that doesn't fix your problem, do the same thing to your criteria.


"prongacct07" wrote:

I have a worksheet that has many conditional sum functions in it. The array
is exported data out of an accounting program. The formating is the same
between the criteria and the range however I can not seem to get it to work
when I export new data it just comes back with the answer of zero which is
incorrect. I have other worksheets with the same set up except one of the
criteria has letters and numbers in the criteria and they work fine. In the
worksheet that does not work that same criteria is just numbers. Can someone
help me with this issue?

Once Excel has treated a number as text, changing the format of the cell
containing it to a numeric format DOES NOT cause Excel to treat it as a
number. You have to force it to a number. The easiest way is to follow the
process I described earlier of copying a blank cell, selecting the data you
want to be numeric, and using Edit-Paste special-Add

"prongacct07" wrote:

Thanks for the help. The criteria and the range was already formated as text
manually. the sum column however is still in number format. I have gone
back a reformatted the range and criteria to both be numbers and it still
does not work.

"Duke Carey" wrote:

either your criteria is a text value that LOOKS like a number, or the data
from the accounting program is text. Copy an empty cell (Ctrl-C), select all
the supposedly numeric data from the accounting program, and use Edit-Paste
Special- Add You are adding zero, so the numeric values don't change.
However, if Excel thought they were text, this operation forces Excel to
treat them as numbers.

If that doesn't fix your problem, do the same thing to your criteria.


"prongacct07" wrote:

I have a worksheet that has many conditional sum functions in it. The array
is exported data out of an accounting program. The formating is the same
between the criteria and the range however I can not seem to get it to work
when I export new data it just comes back with the answer of zero which is
incorrect. I have other worksheets with the same set up except one of the
criteria has letters and numbers in the criteria and they work fine. In the
worksheet that does not work that same criteria is just numbers. Can someone
help me with this issue?