Sumproduct not working when summing values between two numbers

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)*(--Data!$E$2:$E$5000))
>
> Regards,
> KL
>
>
> "FlamencoKid" <FlamencoKid@discussions.microsoft.com> wrote in message
> news:1D4E50C9-8583-4CA1-8214-1A16A8DBEB27@microsoft.com...
> > 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" <FlamencoKid@discussions.microsoft.com> wrote in message
> >> news:35BA1BE9-8F54-4E85-B183-6AAA3FD68E26@microsoft.com...
> >> > 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.
> >>
> >>
>
>
>

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


"FlamencoKid" <FlamencoKid@discussions.microsoft.com> wrote in message
news:D6B5ADF4-05EA-4FF4-BCFB-83073DFD679C@microsoft.com...
> 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)*(--Data!$E$2:$E$5000))
>>
>> Regards,
>> KL
>>
>>
>> "FlamencoKid" <FlamencoKid@discussions.microsoft.com> wrote in message
>> news:1D4E50C9-8583-4CA1-8214-1A16A8DBEB27@microsoft.com...
>> > 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" <FlamencoKid@discussions.microsoft.com> wrote in message
>> >> news:35BA1BE9-8F54-4E85-B183-6AAA3FD68E26@microsoft.com...
>> >> > 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.
>> >>
>> >>
>>
>>
>>

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!

"KL" wrote:

> 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
>
>
> "FlamencoKid" <FlamencoKid@discussions.microsoft.com> wrote in message
> news:D6B5ADF4-05EA-4FF4-BCFB-83073DFD679C@microsoft.com...
> > 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)*(--Data!$E$2:$E$5000))
> >>
> >> Regards,
> >> KL
> >>
> >>
> >> "FlamencoKid" <FlamencoKid@discussions.microsoft.com> wrote in message
> >> news:1D4E50C9-8583-4CA1-8214-1A16A8DBEB27@microsoft.com...
> >> > 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" <FlamencoKid@discussions.microsoft.com> wrote in message
> >> >> news:35BA1BE9-8F54-4E85-B183-6AAA3FD68E26@microsoft.com...
> >> >> > 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.
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

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" <FlamencoKid@discussions.microsoft.com> wrote in message
news:D6B5ADF4-05EA-4FF4-BCFB-83073DFD679C@microsoft.com...
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" <FlamencoKid@discussions.microsoft.com> wrote in message
> news:1D4E50C9-8583-4CA1-8214-1A16A8DBEB27@microsoft.com...
> > 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" <FlamencoKid@discussions.microsoft.com> wrote in message
> >> news:35BA1BE9-8F54-4E85-B183-6AAA3FD68E26@microsoft.com...
> >> > 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.
> >>
> >>
>
>
>