# Sumproduct DIV/0 Error

1. ## Sumproduct DIV/0 Error

I am trying to avoid the divide by 0 error when doing a sum product formula. All criteria involved has blanks. I know that sumproduct turns blanks into 0's. I just want to to avoid that issue. I used this formula among others: =SUMPRODUCT(ISNUMBER(B2:B6/A2:A6)*1)/COUNTA(B2:B6)-1. The base formula is=SUMPRODUCT(B2:B6/A2:A6)/COUNTA(B2:B6)-1. See attached file.  Register To Reply

2. ## Re: Sumproduct DIV/0 Error

I don't see the issue but to avoid the div/0 issue add iferror to the front and ,0 or ,"" to the end...
like this... =IFERROR(SUMPRODUCT(ISNUMBER(B2:B6/A2:A6)*1)/COUNTA(B2:B6)-1,"")

EDIT: oh, and you can change the formulas in col C to this too... =IFERROR(B2/A2-1,"")  Register To Reply

3. ## Re: Sumproduct DIV/0 Error

Thank you for the quick response. I attached a different version of the initial document. I am sorry for the inconvenience. The formula I used in my initial request gives a wrong answer. I need to get the answer 1.61 or 161%. Under manual formula in the document is the formula I used, which now gives a DIV/0 answer. Please see update and let me know if there are still problems.  Register To Reply

4. ## Re: Sumproduct DIV/0 Error

I can't get to the number you want no matter how many permutations I try. So just so I understand, you want the average percent difference of the numbers in column B from the numbers in column A. But I get 142%, not 161% though I see how you got that by averaging the percent differences row by row.  Register To Reply

5. ## Re: Sumproduct DIV/0 Error

Yes thats what I want. It's tough because normally I can find an answer online but today I haven't. What did you do to get 142?  Register To Reply

6. ## Re: Sumproduct DIV/0 Error

I'm hesitant to tell you how I got the 142 yet because I'm not sure that is correct. I'm testing things with my own numbers to see if I can build a formula that gets what I know the answer with my own numbers is. For example, I put a series of 10s in one column and a series of 20s in the other so each is 100% increase and the average overall would be 100%. Now I'm trying to reverse engineer the formula to return that result with sums and counts or sumproduct and maybe count but so... in the mean time maybe someone will drop by and get it quicker than I will.   Register To Reply

7. ## Re: Sumproduct DIV/0 Error

No problem. You've been very helpful so far!  Register To Reply

8. ## Re: Sumproduct DIV/0 Error

this gets me the right answer on my experimental data even with blanks... =(SUM(B2:B6)/SUM(A2:A6))-1
but it returns the 42% for your data set.  Register To Reply

9. ## Re: Sumproduct DIV/0 Error

Thanks again for you help. This is a tricky one. It may not be possible.  Register To Reply

10. ## Re: Sumproduct DIV/0 Error

keep in mind you are trying to average percent differences for a row by row basis, it may be that that is not the way to get an average of the overall percent difference between two columns.
each time I apply that formula to my test data I get the correct percent average.
are you absolutely sure the 161% is correct?
if I take the sum of col A it is 19, the sum of col B is 27, taking (27-19)-19 gives me 42.1%
I'll be interested in seeing what else is offered.  Register To Reply

11. ## Re: Sumproduct DIV/0 Error

I'm sure it is possible. Is it necessary to do this all in one single cell with the sumproduct formula? I changed the C formula to something like =IFERROR(B2/A2-1,"text") and copied down. The correct result was shown in C7, because the AVERAGE() function would ignore the text string from the IFERROR() function which trapped the 0/0 error when the cells were blank.

Will that approach work, or is it necessary to use a single cell SUMPRODUCT() formula?  Register To Reply

12. ## Re: Sumproduct DIV/0 Error

Its correct. The reason I want the 161% method is because I am trying to determine the average % saved on individual deals. The sales themselves could have large dollar differences. For instance one deal may save \$1000 at 1% change (\$90000 and 80000) and the other might save \$1000 at 11% change (\$9000 and \$8000). The 42% gives me the average % saved on all deals when combined. in the example total dollars saved is 2% using the 42% method but 6% for each individual deal using the 161% method. I want to focus on individual averages not totals. Hopefully that makes sense.  Register To Reply

13. ## Re: Sumproduct DIV/0 Error

Its an option definitely. The way you're talking about was the original way I did it, but I feared it would take up too much space because I'm going to have 100s of rows utilizing that method across 50+ columns. With the single formula I would just be able to condense the document to utilize less formulas and less columns. But it would definitely work.  Register To Reply

14. ## Re: Sumproduct DIV/0 Error

I feared it would take up too much space because I'm going to have 100s of rows utilizing that method across 50+ columns.
I guess you have to decide for yourself how much space is too much. You describe using only 100 out of 1million available rows and only 50 or so out of 16000 available columns, so it seems to me that you have a lot of spreadsheet real estate to expand into. Certainly, at some point a spreadsheet gets large enough that save/load/calculate operations start to take a noticeable amount of time, but 100x50 doesn't seem like it should even be close to that point.  Register To Reply