Sumproduct with Division

Question

I'm relatively basic with excel.

I'm trying to calculate a Sumproduct with division and will have =0/0 situations, this results in #DIV/0!. How do I tell the formula to ignore these results and continue dividing the remaining numbers?

My actual spreadsheet has 50+ rows and a variation of rows have blank (greyed out cells) under D & E because values are not required. And I really do not want to add anything to these cells.

I'm looking for a function that can be inserted into the formula below? The answer (in yellow box) should be 98.

Answer 1

Try below:

=SUMPRODUCT(IFERROR((A2:A3="slab")*(B2:B3="house")*(D2:D3="10M")*(C2:C3/18)/(E2:E3/12)^2,0))*2*1.05

Use the ROUND function on the above:

=ROUNDUP(SUMPRODUCT(IFERROR((A2:A3="slab")*(B2:B3="house")*(D2:D3="10M")*(C2:C3/18)/(E2:E3/12)^2,0))*2*1.05,0)

Regards,

Amit Tandon

www.excelanytime.com

Answer 2

Great thank you Amit!! I did try using this formula, but I narrowed it to the (C2:C3/18)/(E2:E3/12)^2 portion only and was having no luck. Thanks!

Answer 3

You can use the IfError() function to suppress that code:

https://support.microsoft.com/en-us/office/how-to-correct-a-div-0-error-3a5a18a9-8d80-4ebb-a908-39e759a009a5

.

MS Support Tips on Error Handling /Avoid broken formulas– (8 Error codes)

https://support.office.com/en-us/article/how-to-avoid-broken-formulas-8309381d-33e8-42f6-b889-84ef6df1d586

If Excel can’t resolve a formula you’re trying to create, you may get an error message like this one:

Unfortunately, this means that Excel can’t understand what you’re trying to do, so you might just want to get out of here and start over.

Excel Formula Errors – (9 Error codes)

https://exceljet.net/excel-formula-errors

The more formulas you write, the more errors you'll run into :)  Although frustrating, formula errors are useful, because they tell you clearly that something is wrong. This is much better than not knowing.  The most disastrous Excel mistakes usually come from normal-looking formulas that quietly return incorrect results. When you run into a formula error, don't panic. Stay calm and methodically investigate until you find the cause. Ask yourself, "What is this error telling me?" Experiment with trial and error. As you gain more experience, you'll be able to avoid many errors, and more quickly correct errors that do arise.

.  *  a basic process for fixing errors

.  *  Trapping errors is a technique where you anticipate and "catch" errors.

.  *  Excel's 9 error codes: #DIV/0!, #NAME?, #N/A, #NUM!, #VALUE!, #REF!, #NULL!, ####, #SPILL!