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" wrote in message
...
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 we
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)*(A 1: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