Sumproduct not working when summing values between two numbers

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,

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



"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.

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.

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

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

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

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

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