Some thoughts. Excel simply treats any TEXT as greater than the largest real
number in its numeric calculations. This can be misleading. If your intent is
to do pure lookup, stick to using index/match or vlookup for the job, do not
use sumproduct. And slant the core matching to be all text comparisons
(instead of trying to match by real numbers), set for exact matching. You
could use: =vlookup(A2&"",...) instead of: =vlookup(A2,...)
to convert numbers to text numbers (the: &"" bit will make it into text)
--
Max
Singapore
---
"Marlaine" wrote:
I am developing a skookum s/s where I am returning values based on various
criteria and am using sumproduct. The issue seems to be with the format of a
cell.

Here is one of my calculations.
=SUMPRODUCT((Actuals!$A$2:$A$7500<"Q4")*(Actuals!$ D$2:$D$7500="40TKB")*(Actuals!$B$2:$B$7500=50)*Act uals!$J$2:$J$7500)

It is returning zero. However, if I put the 50 in quotes, it will return
the correct value. My issue tho is that some of the calculations don't need
quotes and some do. Eg, if I put "6531" in the following equation it will
return 0.
=SUMPRODUCT((Actuals!$A$2:$A$7500<"Q3")*(Actuals!$ D$2:$D$7500="40TKB")*(Actuals!$F$2:$F$7500=6531)*A ctuals!$J$2:$J$7500)

I have not formatted the cells at all but they are exported from a home
grown system. I've played around with changing the format of the columns to
Number and general with no change. Any help would be great as I do not trust
my formulas as yet


--
Thanks
Marlaine