Sumproduct not working when summing values between two numbers

[FlamencoKid]

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.

 

[KL]

Hi FlamencoKid,

If the condition is that a value in column [C] is greater than or equal to
100,000 and 199,999 at the same time, then the only values that would
qualify would be greater or equal to 199,999

Is that what you are after?

Regards,
KL

 

[Ragdyer]

Change:

$C$2:$C$5000>=199999

TO:

$C$2:$C$5000<=199999

 

[FlamencoKid]

Sorry, typo on my part! Should have been <=199999 and it still doesn't work

Any thoughts?

 

[KL]

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)*(--Data!$E$2:$E$5000))

Regards,
KL

 

[FlamencoKid]

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]

Hi FlamencoKid,

This probably suggests that some of the ranges used contain text values that
can not be forced into a number. I would rather try Data>Text to Columns...
etc.

Regards,
KL

 

[RagDyeR]

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 !
----------------------------------------------------------------------------
-------------------

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

 

[FlamencoKid]

Hi

Yeah, I'd imported the codes (and some other data I was using) and it looks
like they were treated as text. As soon as I resolved that it sorted the
problem. Thanks very much for your help and sorry for the typo at the start
of all this that complicated matters!