View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.programming
Rick Rothstein \(MVP - VB\) Rick Rothstein \(MVP - VB\) is offline
external usenet poster
 
Posts: 2,202
Default ARRAY MULTI LOGICAL OPERATOR

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)*(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