Re: Sumproduct not working when summing values between two numbers
- From: "FlamencoKid" <FlamencoKid@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Tue, 13 Sep 2005 10:01:04 -0700
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@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:D6B5ADF4-05EA-4FF4-BCFB-83073DFD679C@xxxxxxxxxxxxxxxx
> > 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@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> >> news:1D4E50C9-8583-4CA1-8214-1A16A8DBEB27@xxxxxxxxxxxxxxxx
> >> > 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@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> >> >> news:35BA1BE9-8F54-4E85-B183-6AAA3FD68E26@xxxxxxxxxxxxxxxx
> >> >> > 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.
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>
.
- References:
- Sumproduct not working when summing values between two numbers
- From: FlamencoKid
- Re: Sumproduct not working when summing values between two numbers
- From: Ragdyer
- Re: Sumproduct not working when summing values between two numbers
- From: KL
- Re: Sumproduct not working when summing values between two numbers
- From: FlamencoKid
- Sumproduct not working when summing values between two numbers
- Prev by Date: Multiple If Statements
- Next by Date: Re: Can excel sort entire rows of data like access?
- Previous by thread: Re: Sumproduct not working when summing values between two numbers
- Next by thread: Multiple coin flips in a single cell
- Index(es):
Relevant Pages
|
