Sumproduct has the advantage of performing as an array function *without*
actually being an array formula.
On the other hand, it *only* works when the return is numerical.

Now, you used the asterisk form of Sumproduct, which I do prefer, because it
warns you if your data is contaminated (contains alpha text) with an error
message (#VALUE!).
Since you have *not* received an error message, that means that the data in
Column E *is* numerical.
Notice, I didn't say text, because with the asterisk form of Sumproduct,
text numbers *are* properly evaluated along with real numbers.

So that leaves Column C as your problem data.

Do you import your data?
If you do, that opens up a whole other can of worms.

Try this formula and post back with the results:

=SUMPRODUCT((Data!$C$2:$C$5000="100000")*(Data!$C $2:$C$5000<="199999")*(Dat
a!$E$2:$E$5000))

--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

"FlamencoKid" wrote in message
...
Hi

Tried your suggestion and the result was #VALUE! Does this mean the source
data was text? If so, should I Edit Paste Special and turn it to a value?

Thanks a lot for your help

"KL" wrote:

Then the only reasonable explanation I see is that your values are in
reality text strings and not numeric values. As a test try the following
formula and if it works then you know where the issue is:


=SUMPRODUCT((--Data!$C$2:$C$5000=100000)*(--Data!$C$2:$C$5000<=199999)*(--D
ata!$E$2:$E$5000))

Regards,
KL


"FlamencoKid" wrote in message
...
Sorry, typo on my part! Should have been <=199999 and it still doesn't
work :)

Any thoughts?

"Ragdyer" wrote:

Change:

$C$2:$C$5000=199999

TO:

$C$2:$C$5000<=199999
--
HTH,

RD


-------------------------------------------------------------------------
--
Please keep all correspondence within the NewsGroup, so all may benefit
!

-------------------------------------------------------------------------
--
"FlamencoKid" wrote in message
...
Hi

Values for criteria that I'm looking at are in column C, values to
sum
are
in column E. I'm trying to add together (not count) all the amounts
in
column
E that have corresponding values in C between two numbers. The
following
formula produces 0 when I know (I can see) that there are rows that
match
the
criteria.

The numbers in column C are codes for products by the way and they
won't
necessarily be consecutive.


=sumproduct((Data!$C$2:$C$5000=100000)*(Data!$C$2 :$C$5000=199999)*(Data!$E
$2:$E$5000))

Any help greatly appreciated - this is driving me nuts! I swear I got
it
working at one stage but now it no longer seems to work.