If you're using Excel 2003, convert your data into a list...

Data List Create List

The ranges will automatically adjust as data is added/removed.
Otherwise, use dynamic named ranges...

Insert Name Define

Name: ColA

Refers to:

=$A$5:INDEX($A$5:$A$65536,MATCH(9.99999999999999E+ 307,$A$5:$A$65536))

Click Add

Name: ColB

Refers to:

=$B$5:INDEX($B$5:$B$65536,MATCH(9.99999999999999E+ 307,$A$5:$A$65536))

Click Add

Name: ColC

Refers to:

=$C$5:INDEX($C$5:$C$65536,MATCH(9.99999999999999E+ 307,$A$5:$A$65536))

Click Ok

Then try the following formula....

=SUMPRODUCT(--(ColA7000000),--(ColA<7140999),--(RIGHT(ColA,3)="150"),--(
ColB="GL-2"),ColC)

Hope this helps!

http://www.xl-central.com

In article ,
Tim wrote:

Here is what I am trying to do:
A B C
7000000 GL-2 $100
7126150 AR-PY $200
7135250 GL-2 $300
7137150 GL-2 $400
7139150 GL-2 $500
7145975 GL-2 $600
First...I need to check if numbers in A column are greater than 7000000 and
less than 7140999. Next identify only numbers that end in 150. Next check
column B and identify on the GL-2 cells...finally add the values in column C.
So the answer here should be $900.
=SUMPRODUCT((A5:A107000000)*(A5:A10<7140999)*(1*R IGHT(A5:A10,3)=150)*(B5:B10=
"GL-2"),C5:C10)
The above works (thanks Ashish), BUT...I don't know how many rows for sure I
need to scan down)...so A10, may be A945 or A466. How can I tell it how many
rows?