• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

DIV/0 error in SUMPRODUCT formula

Am trying to obtain sumproduct of two rows having data (One of rows may have zeros and the other row is the derived value of each row value divided by itself)

Ex Data:

ROW 1 ROW 2

100 0
30 30
400 30
500 30


USED FORMULA:

=SUMPRODUCT(F4:F7,G4:G7/G4:G7)

ERROR :
#DIV/0!

Please help on this ....
 
Hi Mallikarjun....

Your Explnation is not quite clear, it will help if you can upload a file with sample data set and desired result.

Based on my understanding u can use below formula and see if it works.

=SUMPRODUCT(F4:F7,IFERROR((G4:G7/G4:G7),0))

Enter with CTRL+SHIFT+ENTER
 
The error "#DIV/0" means you are trying to divide by zero. So you must have a zero somewhere in the array G4:G7.

So looking at the G4:G7 / G4:G7 part... this seems a weird piece of code to me... what are you trying to get? Because every number divided by itself should be one (unless it's zero), any non-number should return an error (and SUMPRODUCT can't handle errors). Judging by the formula, you simply want to sum the array F4:F7 if G4:G7 has a number. If so, try the SUMIF and ISNUMBER function.

Edit:
If it seems I didn't read your post completely, then you are right (sorry)... I'm a bit sleepy :(
 
Last edited:
Some times row 2 could have zero values and also row 2 values could be derived by dividing data from other row as well.

Pl find the modified sheet.
 

Attachments

  • SUMPRODUCT.xlsx
    9.4 KB · Views: 15
Hi There,

Enter the following formula as array (ctrl + shift + enter):
Code:
=SUM(IFERROR($B$4:$B$9*($C$4:$C$9/$D$4:$D$9),))
 
Hi Reddy G
As you quoted data in your excel file and your required output should be 5557.89
You can type the formula and press Ctrl+Shift+Enter
=SUMPRODUCT(B4:B9,IFERROR((C4:C9/D4:D9),0))
Output will be 5557.89

Regards,
Muneer
 
Hi, Mallikarjuna Reddy G!
Your formula at C10 cell works fine, if you enter as an array formula. Remember that array formulas should be entered with Ctrl-Shift-Enter instead of just Enter.
Regards!
 
Hi All,

SUMPRODUCT(B4:B9,IFERROR((C4:C9/C4:C9),0)) this formula is working fine ... as suggested control+shift+enter is the key here :)

Thanks a ton!

Cheers!
 
Hi, Mallikarjuna Reddy G!
Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.
Regards!
 
Back
Top