Hi,

I'm trying to get a sum, in an array, but I'm getting lost when I try to
involve an OR operator.

I need to sum the codes beginning with "5" but not the ones among "514000"
and "514020".

You could assume all code are converted to string, so first one would be
'501010' and so on...

CODE UNITS
501010 150.00
501030 347.52
501070 150.00
504010 130.06
504020 70.00
505030 50.00
506020 25.00
506060 25.00
507010 10.00
508010 20.84
508030 3.00
509010 5.86
509020 1.54
509030 1.19
51000N 10.00
513020 4.00
513690 3.00
514020 0.30
516870 1.75
600040 25.00


Could any one give me a hand on this?

TIA,

=SUMPRODUCT(--(LEFT(A2:A21,1)="5"),--(B2:B21))-SUMPRODUCT(--((A2:A21)="514000"),--(B2:B21))-SUMPRODUCT(--((A2:A21)="514020"),--(B2:B21))

--
Gary''s Student - gsnu200749


"sharon" wrote:

> Hi,
>
> I'm trying to get a sum, in an array, but I'm getting lost when I try to
> involve an OR operator.
>
> I need to sum the codes beginning with "5" but not the ones among "514000"
> and "514020".
>
> You could assume all code are converted to string, so first one would be
> '501010' and so on...
>
> CODE UNITS
> 501010 150.00
> 501030 347.52
> 501070 150.00
> 504010 130.06
> 504020 70.00
> 505030 50.00
> 506020 25.00
> 506060 25.00
> 507010 10.00
> 508010 20.84
> 508030 3.00
> 509010 5.86
> 509020 1.54
> 509030 1.19
> 51000N 10.00
> 513020 4.00
> 513690 3.00
> 514020 0.30
> 516870 1.75
> 600040 25.00
>
>
> Could any one give me a hand on this?
>
> TIA,

Give this formula a try....

=SUMPRODUCT((LEFT(A1:A20)="5")*(A1:A20<>514000)*(A1:A20<>514020)*B1:B20)

Rick


"sharon" <(E-Mail Removed)> wrote in message
news:861E2F51-FD67-4A0E-9D2C-(E-Mail Removed)...
> Hi,
>
> I'm trying to get a sum, in an array, but I'm getting lost when I try to
> involve an OR operator.
>
> I need to sum the codes beginning with "5" but not the ones among "514000"
> and "514020".
>
> You could assume all code are converted to string, so first one would be
> '501010' and so on...
>
> CODE UNITS
> 501010 150.00
> 501030 347.52
> 501070 150.00
> 504010 130.06
> 504020 70.00
> 505030 50.00
> 506020 25.00
> 506060 25.00
> 507010 10.00
> 508010 20.84
> 508030 3.00
> 509010 5.86
> 509020 1.54
> 509030 1.19
> 51000N 10.00
> 513020 4.00
> 513690 3.00
> 514020 0.30
> 516870 1.75
> 600040 25.00
>
>
> Could any one give me a hand on this?
>
> TIA,

Thanks Gary and Rich for your help.

I realize I had an error while writting my needs, in fact what I need te sum
of all the values, starting with "5" but not the values BETWEEN "514000" and
"514020" ( I typed among), so I don't want "514000", "514010", "514020"...)

May be this changes the answer, because my trouble is when adding an OR
operator.

Gary, when you type 2 "-" signs I guess it means it's an array formula.
Isn't it?

TIA,


"Gary''s Student" wrote:

> =SUMPRODUCT(--(LEFT(A2:A21,1)="5"),--(B2:B21))-SUMPRODUCT(--((A2:A21)="514000"),--(B2:B21))-SUMPRODUCT(--((A2:A21)="514020"),--(B2:B21))
>
> --
> Gary''s Student - gsnu200749
>
>
> "sharon" wrote:
>
> > Hi,
> >
> > I'm trying to get a sum, in an array, but I'm getting lost when I try to
> > involve an OR operator.
> >
> > I need to sum the codes beginning with "5" but not the ones among "514000"
> > and "514020".
> >
> > You could assume all code are converted to string, so first one would be
> > '501010' and so on...
> >
> > CODE UNITS
> > 501010 150.00
> > 501030 347.52
> > 501070 150.00
> > 504010 130.06
> > 504020 70.00
> > 505030 50.00
> > 506020 25.00
> > 506060 25.00
> > 507010 10.00
> > 508010 20.84
> > 508030 3.00
> > 509010 5.86
> > 509020 1.54
> > 509030 1.19
> > 51000N 10.00
> > 513020 4.00
> > 513690 3.00
> > 514020 0.30
> > 516870 1.75
> > 600040 25.00
> >
> >
> > Could any one give me a hand on this?
> >
> > TIA,

> Thanks Gary and Rich for your help.

Rick (not Rich)...

> I realize I had an error while writting my needs, in fact what I need te
> sum
> of all the values, starting with "5" but not the values BETWEEN "514000"
> and
> "514020" ( I typed among), so I don't want "514000", "514010",
> "514020"...)

Okay, if I understand you correctly, give this formula a try...

=SUMPRODUCT((LEFT(A1:A20)="5")*(A1:A20>=514000)*(A1:A20<=514020)*B1:B20)

> Gary, when you type 2 "-" signs I guess it means it's an array formula.
> Isn't it?

No, it has nothing to do with an array formula (SUMPRODUCT formulas are
entered normally)... Boolean expressions (those expressions between the
parentheses in our formulas) return TRUE or FALSE... multiplying them by -1
twice (that is what the double minus signs are doing; in the same way the -5
means multiply -1 times 5) converts TRUE to 1 and FALSE to 0.

Rick

Rick,

Thanks again, and ,oops!, sorry for my mistyping !

I think the solution you gave me is not the one I wanted, since it sums just
the values between "514000" and "514020". I thing you use an AND when I need
an OR.

My thoughts were that the rows matching my needs were:
LEFT(CODES,1)="5" AND (OR(CODES<"514000",CODES>"514020")), assuming CODES is
the range where I have the codes (A3:A20) in the example.

But I don't know how to translate this into a fomula...


Thanks for your explanation on SUMPRODUCT sentences.


Cheers,


"Rick Rothstein (MVP - VB)" wrote:

> > Thanks Gary and Rich for your help.
>
> Rick (not Rich)...
>
> > I rlize I had an error while writting my needs, in fact what I need te
> > sum
> > of all the values, starting with "5" but not the values BETWEEN "514000"
> > and
> > "514020" ( I typed among), so I don't want "514000", "514010",
> > "514020"...)
>
> Okay, if I understand you correctly, give this formula a try...
>
> =SUMPRODUCT((LEFT(A1:A20)="5")*(A1:A20>=514000)*(A1:A20<=514020)*B1:B20)
>
> > Gary, when you type 2 "-" signs I guess it means it's an array formula.
> > Isn't it?
>
> No, it has nothing to do with an array formula (SUMPRODUCT formulas are
> entered normally)... Boolean expressions (those expressions between the
> parentheses in our formulas) return TRUE or FALSE... multiplying them by -1
> twice (that is what the double minus signs are doing; in the same way the -5
> means multiply -1 times 5) converts TRUE to 1 and FALSE to 0.
>
> Rick
>
>

Sorry, I misread your question. Check back later because I think Gary's
Student (or someone else) may come up with a more compact formula then the
one I developed; but this should do what you want...

=SUMPRODUCT((LEFT(A1:A20)="5")*B1:B20)-SUMPRODUCT((LEFT(A1:A20)="5")*(A1:A20>=514000)*(A1:A20<=514020)*B1:B20)

Rick


"sharon" <(E-Mail Removed)> wrote in message
news:BCD5F106-8489-47BA-9D9A-(E-Mail Removed)...
> Rick,
>
> Thanks again, and ,oops!, sorry for my mistyping !
>
> I think the solution you gave me is not the one I wanted, since it sums
> just
> the values between "514000" and "514020". I thing you use an AND when I
> need
> an OR.
>
> My thoughts were that the rows matching my needs were:
> LEFT(CODES,1)="5" AND (OR(CODES<"514000",CODES>"514020")), assuming CODES
> is
> the range where I have the codes (A3:A20) in the example.
>
> But I don't know how to translate this into a fomula...
>
>
> Thanks for your explanation on SUMPRODUCT sentences.
>
>
> Cheers,
>
>
> "Rick Rothstein (MVP - VB)" wrote:
>
>> > Thanks Gary and Rich for your help.
>>
>> Rick (not Rich)...
>>
>> > I rlize I had an error while writting my needs, in fact what I need te
>> > sum
>> > of all the values, starting with "5" but not the values BETWEEN
>> > "514000"
>> > and
>> > "514020" ( I typed among), so I don't want "514000", "514010",
>> > "514020"...)
>>
>> Okay, if I understand you correctly, give this formula a try...
>>
>> =SUMPRODUCT((LEFT(A1:A20)="5")*(A1:A20>=514000)*(A1:A20<=514020)*B1:B20)
>>
>> > Gary, when you type 2 "-" signs I guess it means it's an array formula.
>> > Isn't it?
>>
>> No, it has nothing to do with an array formula (SUMPRODUCT formulas are
>> entered normally)... Boolean expressions (those expressions between the
>> parentheses in our formulas) return TRUE or FALSE... multiplying them
>> by -1
>> twice (that is what the double minus signs are doing; in the same way
>> the -5
>> means multiply -1 times 5) converts TRUE to 1 and FALSE to 0.
>>
>> Rick
>>
>>

Hi Rick,

It does what I need, and now I can understand bettter Gary's solution,
although it wasn't exactly what I asked for.

In fact I found a workaround using two parts, so I took codes beginning with
"5" and <"514000" and I sum the ones beginning with "5" and >"514020" .

Nevertheless I like better your solution.



Thanks again, have a nice time!


"Rick Rothstein (MVP - VB)" wrote:

> Sorry, I misread your question. Check back later because I think Gary's
> Student (or someone else) may come up with a more compact formula then the
> one I developed; but this should do what you want...
>
> =SUMPRODUCT((LEFT(A1:A20)="5")*B1:B20)-SUMPRODUCT((LEFT(A1:A20)="5")*(A1:A20>=514000)*(A1:A20<=514020)*B1:B20)
>
> Rick
>
>
> "sharon" <(E-Mail Removed)> wrote in message
> news:BCD5F106-8489-47BA-9D9A-(E-Mail Removed)...
> > Rick,
> >
> > Thanks again, and ,oops!, sorry for my mistyping !
> >
> > I think the solution you gave me is not the one I wanted, since it sums
> > just
> > the values between "514000" and "514020". I thing you use an AND when I
> > need
> > an OR.
> >
> > My thoughts were that the rows matching my needs were:
> > LEFT(CODES,1)="5" AND (OR(CODES<"514000",CODES>"514020")), assuming CODES
> > is
> > the range where I have the codes (A3:A20) in the example.
> >
> > But I don't know how to translate this into a fomula...
> >
> >
> > Thanks for your explanation on SUMPRODUCT sentences.
> >
> >
> > Cheers,
> >
> >
> > "Rick Rothstein (MVP - VB)" wrote:
> >
> >> > Thanks Gary and Rich for your help.
> >>
> >> Rick (not Rich)...
> >>
> >> > I rlize I had an error while writting my needs, in fact what I need te
> >> > sum
> >> > of all the values, starting with "5" but not the values BETWEEN
> >> > "514000"
> >> > and
> >> > "514020" ( I typed among), so I don't want "514000", "514010",
> >> > "514020"...)
> >>
> >> Okay, if I understand you correctly, give this formula a try...
> >>
> >> =SUMPRODUCT((LEFT(A1:A20)="5")*(A1:A20>=514000)*(A1:A20<=514020)*B1:B20)
> >>
> >> > Gary, when you type 2 "-" signs I guess it means it's an array formula.
> >> > Isn't it?
> >>
> >> No, it has nothing to do with an array formula (SUMPRODUCT formulas are
> >> entered normally)... Boolean expressions (those expressions between the
> >> parentheses in our formulas) return TRUE or FALSE... multiplying them
> >> by -1
> >> twice (that is what the double minus signs are doing; in the same way
> >> the -5
> >> means multiply -1 times 5) converts TRUE to 1 and FALSE to 0.
> >>
> >> Rick
> >>
> >>
>
>