I am getting better at converting array formulas to sumproducts, but this one seems to be extra difficult. The table i am working with has #N/A and or other errors to exclude also.

I can get similar sumproduct array formulas to work, but not this one. Any suggestions?

array formula {=SUM(IF( ( (LEFT('Working Sheet'!$A$1:$A$20000,2)= "51") + (LEFT('Working Sheet'!$A$1:$A$20000,2)= "52") + (LEFT('Working Sheet'!$A$1:$A$20000,2)= "53") ) * NOT(ISERROR('Working Sheet'!N$1:N$20000) ),('Working Sheet'!N$1:N$20000),"-")) }

The Sheet 'Working Sheet' A1:a20000 has a 4 digit number that I need to match the first 2. Due to other constraints I was trying not to create a new column with just the 2 digits of the 4 digit number.

The closes sumproduct I came up with was:

=SUMPRODUCT( ( --(LEFT('Working Sheet'!$A$1:$A$20000,2)= "51") + --(LEFT('Working Sheet'!$A$1:$A$20000,2)= "52") + --(LEFT('Working Sheet'!$A$1:$A$20000,2)= "53") ) * --NOT(ISERROR('Working Sheet'!N$1:N$20000) ),('Working Sheet'!N$1:N$20000))

the return is #N/A

Any suggestions for for a sumproduct or other steps to make optimize and make more efficient would be appreciated.

Thanks

Alan

P.S. If anyone has a a suggestion on using index or better function that is not volatile (faster) instead of using indirect to reference other sheets (or even files) as in

=INDIRECT("'" & B1& "'"&"!" & "A1")

=INDIRECT("'" & B1& "'"&"!" & C1)

please check out

http://www.mrexcel.com/board2/viewtopic.php?t=134225&start=0&postdays=0&postorder=asc&highlight=