How can i shorten up this function / formula?

Just wondering if there is a way to shorten this up? I need to add some more conditions, but if i do that, it's going to get really really really long...

SUMPRODUCT(--($B$2:$B$525=A4001)*(($E$2:$E$525="Local Loop")+($E$2:$E$525="Long Haul")+($E$2:$E$525="Loop A")+($E$2:$E$525="loop z")),--(($M$2:$M$525="live")+($M$2:$M$525="Pending - Cancellation")),$BX$2:$BX$525)

Also, is there anyway to extend the range of what i'm searching for? The formula i have above isn't entirely correct b/c if I go to 525, it comes up with n/a. If i have it at 346, i get a result.

i'll definitely have to try that, thank you

With the original suggestion, I only get a value of zero, even when I had values of higher numbers with the original long formula version.

I tried to edit the formula you suggested by removing the ,0 from the end of each match statement, but that did not change anything.

However, I am DEFINITELY going to pursue the second option you gave me with the list That will make everything so much easier.

I keep getting value errors when trying to use a table to look up the comparable values. If I don't get a value error, then I end up getting a result of 0.

Please Login or Register  to view this content.

If i use my old formula, I can get it to work, but it is lengthy and not as clean as what you suggested.

Please Login or Register  to view this content.

I've put a table on here as well so you can see what I am doing.

(ignore the circular error..i was just trying something out at the bottom) !!

Thanks again Ron, your help has been tremendous. I can get the formula to work as you provided it, for the small sample I attached.

However, when I move the formula to a spot deep within my actual spreadsheet, I only receive a "0" value for the data. The changed formula I'm using is below. I compared it to what you provided, and it is identical other than the ranges of where to look up the values.

Any ideas why I would only get 0's as a result?

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Here is a trimmed down version of the real spreadsheet I am using.

I put the formula and data tables starting in cell DA3879, which should be pointing back to the raw data I have at the top left section of the spreadsheet.

For some reason, when I have it seperated out like this, I only get 0's for results instead of the true values. As I mentioned before, on the small sample spreadsheet, I could get the original formula you helped me with to work.

Thanks again for all of your help. These forums have proven to be an invaluable resource!

I love you Simple mistakes on my part that for some reason I couldn't find...even after staring at it for hours and hours. You can consider this topic resolved!!

Thanks again for your help.