How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask The-PC-Guy Your Own Question
The-PC-Guy
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
A new question is answered every 9 seconds

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

This answer was rated:

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 :

Thanks for using JustAnswer. My name isXXXXX will do whatever I can to answer your question

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 :

instead of a blank

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
The-PC-Guy and 2 other Microsoft Office Specialists are ready to help you

Related Microsoft Office Questions