Sum Product Errors in Excel Errors

I have this formula and it is giving me grief, I keep getting errors (#DIV/0!). I have checked online and apparently sum product does not like blanks. I was wondering if someone could help me out on how I can get sumproduct to skip the blank cells.

=SUMPRODUCT((N2:N8/M2:M8>85)*(M2:M8))

Basically column M is a list of orders while N is a list of the total weight of those orders. I need to sum the orders column if (Charge Weight/Orders) is greater than 85. so basically IF CHARGE WEIGHT "divided by" ORDERS>85 then sum. Hopefully this makes sense.

Also Note, this is just a sample data, the actual data can be thousands and thousands of rows.

enter image description here

728x90

1 Answers Sum Product Errors in Excel Errors

There is a number of ways of doing it, the simplest one would be:

 =SUMPRODUCT(IFERROR(((ChargeWeight/Shippers)>85),0)*(Shippers))

4 months ago