• 100% Satisfaction Guarantee
The-PC-Guy, Computer Manager
Category: Microsoft Office
Satisfied Customers: 1938
Experience:  20 years experience providing remote computer support
62934938
Type Your Microsoft Office Question Here...
The-PC-Guy is online now

# I have a formula that seems to work inconsistently and i need

I have a formula that seems to work inconsistently and i need it to work all the time - what I need to happen is to find the sum of four cells each being multiplied by a constant and it works great when the four cells have a value but when one or more of the cells is missing a value the formula does not return a value at all and I need the partial value for the included cells. My formula is: =IFERROR(SUM(\$M12*10,\$O12*5,\$Q12*5,\$S12*5),"").

I believe the problem is that the cell that does not contain a value DOES contain a formula that does not return a value in some cases: =IFERROR(IF(\$E12="F",VLOOKUP(\$K12,Table!\$B\$3:\$E\$1024,4,FALSE),VLOOKUP(\$K12,Table!\$B\$3:\$E\$1024,3,FALSE)),"").

So I guess my question can I revise my formula to work even though one of the four cells contains a formula and NOT A VALUE?
Thanks

The-PC-Guy :

The-PC-Guy :

could you possibly send me a copy of the sheet

The-PC-Guy :

so I can get a visual idea of where the problem might be occuring

The-PC-Guy :

give me a minute to open it

Customer:

The formula is in cell X12.

The-PC-Guy :

do you care for the lookup formulas

The-PC-Guy :

if it puts a 0 in when there is an error

The-PC-Guy :

The-PC-Guy :

that will make the sum formula work

The-PC-Guy :

actually that is the only way to fix the sum formula

The-PC-Guy :

still with me?

The-PC-Guy :

actually yes that will fix it for you

The-PC-Guy :

change all of the iferror ""

The-PC-Guy :

to iferror 0

The-PC-Guy :

here is the modified file

The-PC-Guy :

PLEASE REMEMBER TO RATE MY SERVICE

The-PC-Guy :

and remember I am arround if you need anything else

Customer:

Yes that works but then the 0 screws up with the data I am calculating etc. So I will then have to delete all the zeros or delete all the formulas from the blank cells.

Customer:

I will see which way works best for me as either way it is a band aid fix. Thanks for your help.

The-PC-Guy :

i have another idea

The-PC-Guy :

if you can't have the zeros

The-PC-Guy :

if its only the 4 cells

ok

i fixed it for you so you can keep your blanks and still have the sum formula work

http://ge.tt/2ogQ6wt/v/0?c