dealing with errors in an array

[weeble33]

Hello

=SUMPRODUCT((LEFT($B$4:$B$1000,2)="BP")*$Y$4:$Y$1000)

I have the above formula, but some of the cells in B:B have a #value error. I tried putting up an iferror() function, but it doesnt seem to work with array functions. Any suggestions on how to get around this?

[xsoldoutx]

Wondering if there are blanks in your cells or other values, maybe try something like the following

=SUMPRODUCT(--(LEFT($B$4:$B$1000,2)="BP")*($Y$4:$Y$1000<>""),($Y$4:$Y$1000))

[weeble33]

There are some blanks, but in the formula evaluation, they are returned as false and thus handled appropriately. Sorry, but the "--" coercion did not work. Your formula still results in a #Value error

[xsoldoutx]

Check in your column Y and make sure there aren't any characters or letters only numeric values, sorry just trying to cover all the bases.

[weeble33]

The Y column does not contain anything besides numbers and blanks. The problem is dealing with the B:B Column. For clarification, the B:B column contains strings, blanks, and a few #values errors. I have shortened the array to a smaller portion of data where I know there are no #value errors, and the formula works. So the only issue is dealing with the errors.

Now then, I have temporarily nipped this in the butt and added an iferror() function into the formulas in the B:B column, thus replacing all errors with a blank. I'd prefer to have the solution come from the equation above, but if that doesn't happen I at least have a back up.