How do you get excel to ignore division by 0 in a sumproduct?

Question

I have a sum product where I need to divide, so one of the inputs is 1/AL:AL. The problem is that some of those cells are 0. I have tried 1/if(AL:AL=0,1,AL:AL) and iferror(1/AL:AL,1) but both still return #DIV/0!. Here is my actual formula:

=SUMPRODUCT('Data'!$I:$I,'Data'!$AV:$AV,**1/'Data'!$AL:$AL**)

Answer 1

Your formula with IFERROR(1/AL:AL,1) should work if you enter it as an array formula, with the cursor in the formula bar, press CTRL+SHIFT+ENTER.

Answer 2

This should work for you:

=SUMPRODUCT(Data!$I:$I,Data!$AL:$AL*(Data!$AV:$AV<>0)/((Data!$AV:$AV=0)+(Data!$AV:$AV<>0)*Data!$AV:$AV))

However, I highly recommended to never use full column references with array formulas or functions that calculate arrays. Sumproduct is a function that calculates arrays, and using whole column references can be very detrimental to the speed and efficiency of your workbook. Always try to use as limited a range as possible, for example the adjusted formula might look like this:

=SUMPRODUCT(Data!$I$1:$I$10,Data!$AL$1:$AL$10*(Data!$AV$1:$AV$10<>0)/((Data!$AV$1:$AV$10=0)+(Data!$AV$1:$AV$10<>0)*Data!$AV$1:$AV$10))

If you are using whole column references in order to pick up new data as it is entered, please consider using dynamic named ranges or table references.