# #value! error, why? (Stumped)

#### mpartyka

##### Board Regular
Cell W12 contains this formula:

=SUM(G12+H12+I12+J12+K12+M12+O12+Q12+S12+U12)

I cannot change the referenced cells (ie G12 etc, or make it G12:U12)

How can I make cell W12 blank even if the cells in the formula are blank, filled in, or partially filled in? I hope I explained myself well enough?

### Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

#### NdNoviceHlp

##### Well-known Member
Try this. Hope it works. Dave
Code:
``=IF(ISERROR(SUM(G12+H12+I12+J12+K12+M12+O12+Q12+S12+U12) ),"",SUM(G12+H12+I12+J12+K12+M12+O12+Q12+S12+U12) )``

#### ken2step

##### Well-known Member
Not totally clear when you want a blank to appear???? Do you mean blank until all cells have something in them? The way it reads you want that cell always blank, and I am sure that is not your intent.

#### KenWright

##### Active Member
Are you putting text in any of those cells, else I',m not sure how you're getting your value error.

Your formula syntax is incorrect in that you don't need SUM to wrap cell references that have + between them, or you just lose the + eg

=G12+H12+I12+J12+K12+M12+O12+Q12+S12+U12

or

=SUM(G12,H12,I12,J12,K12,M12,O12,Q12,S12,U12)

Either way a good portion of your ranges are contiguous so you can use a range eg

=SUM(G12:K12)+M12+O12+Q12+S12+U12

To then give a blank if no data you could then use the construct (Assuming no negative values that could net the series to 0)

=IF((SUM(G12:K12)+M12+O12+Q12+S12+U12)=0,"",SUM(G12:K12)+M12+O12+Q12+S12+U12)

#### mpartyka

##### Board Regular
Man was I tired! You are correct, I did not want cell W12 to be blank when all the others had data. The only data that will go into the range cells will be numbers 0 through 6. Thanks for your assistance, I will check it out momentarily.

#### mpartyka

##### Board Regular
Okay, I tried those ideas and I got the same result, #value!. So, I will explain things a little more. I have 10 cells (they actually represent 1/2 day periods) which total into cell W12. Now, if all 10 cells are filled in everything works great. The problem comes into play at the beginning of the week when no 1/2 days have been filled in yet, so all 10 cells are blanks. Taking things a step further, as we go day by day (Mon- Fri) the cells will slowly be filled in with numbers. That range can be 0, 1, 2, or 3. Hypothetically 4, 5, and 6 might possibly be in there but that is highly unlikely in reality. That brings us to the #value! problem and this is what I need:

If (none of the cells), (one of the ten cells), or (9 of the 10 cells) are filled in I desire cell W12 to be blank. Ideally, I would like a running total of the week as each day is filled in but that appears to be another problem altogether? Any ideas? I thank you in advance for the assistance rendered.

The 10 cells in question get their number values from another sheet, if that helps?

#### ken2step

##### Well-known Member
Much more clear!! I am sure someone can help you now....I will be back in about 6 hours and I can help you then if no one has yet. Thanks for the better explaination!

#### Todd Bardoni

##### Well-known Member
=IF(OR(COUNT(G12:K12,M12,O12,Q12,S12,U12)=0,COUNT(G12:K12,M12,O12,Q12,S12,U12)=1,COUNT(G12:K12,M12,O12,Q12,S12,U12)=9),"",SUM(G12+H12+I12+J12+K12+M12+O12+Q12+S12+U12))

#### KenWright

##### Active Member
Now you really have me confused. First you say you want it to return blank unless ALL the cells are filled, but then you say you would like a running total as you go through the week?? You can have one or the other but not both, unless you utilise another cell.

Now lets just clarify:-

• - You have 10 cells that are referred to by the formula, those being the ones above

- Each of those cells gets its values from another sheet, so I assume you have formulas in each of these 10 cells - correct?

- Assuming that is correct, what do those formulas return on the days that nothing has been filled in, ie a 0 a blank or what?

- In your 10 cells, are there are any errors such as #VALUE anywhere.
Lastly, this would probably be easier if you could use Colo's HTMLMaker to upload a copy of this section of your worksheet.

#### mpartyka

##### Board Regular
I think perhaps I do not explain myself well enough at times? (Obvious isn't it) Originally, I did want the result to be blank unless the whole week was filled in, true. Then I thought of running total grade sheets and the idea came why not a running total for this sheet? I did use Todd's example for W12 and then I had to modify the 10 range cell formulas. It all works well now with no type of errors whatsoever. I suspect if I was formally trained in Excel I would better unerstand the nuances of the functions and function combinations. (This is off the record but I cannot for the life of me figure out what is the difference between +or or *or) A lot of trial and error. However, I greatly appreciate all of your time and asisstance. As always, thank you.

Replies
0
Views
424
Replies
3
Views
161
Replies
1
Views
150
Replies
0
Views
165
Replies
7
Views
3K

1,181,992
Messages
5,933,135
Members
436,881
Latest member
CMScons

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back