Formula

[Guest]

I am trying to add up a column, but some of the cells have a space inserted (which need to remain there), however this is giving me #Value! instead of the correct answer - any ideas, please help - it's driving me mad!

Tina

 

[Paul]

I am trying to add up a column, but some of the cells have a space

inserted (which need to remain there), however this is giving me #Value!
instead of the correct answer - any ideas, please help - it's driving me
mad!!

Tina

What formula are you using?
A cell containing a space character will be treated as text.
If you use
=A1+A2+A3
or
=A1+A3
or similar, any cell containing text will give #VALUE! error.
However, if you use
=SUM(A1:A3)
or
=SUM(A1,A3)
text cells will be ignored.

 

[Peo Sjoblom]

=SUM(Range)

will disregard text
Why do you have to use spaces? I have never understood
why people insert spaces in a cell where there is nothing else? If you do it
using a formula use "" instead of " "

--

Regards,

Peo Sjoblom

I am trying to add up a column, but some of the cells have a space

inserted (which need to remain there), however this is giving me #Value!
instead of the correct answer - any ideas, please help - it's driving me
mad!!

 

[Guest]

Thanks for that, I am trying to add the following

A B
1 . 10 10 1
2. 10 10 1
3. 20 20 20 Sub Tota
4
5.
6
7
8. Sub Tota
9
10.20 20 20. Total (This is what should be displayed

This is a much smaller version I am trying to add about 30 subtotals.

The blank cells to have an if statement in which did insert a blank ie. " ", but I have changed this as suggested to "", but this still returns the same answer of #value!, is it possible to get round it by adding A3+A9+A15 etc etc

Thanks Tin

 

[Dave Peterson]

How did you add the subtotal rows? If you did them manually, maybe you could
try this on a backup copy of your workbook:

Add some nice headers.
Remove those manual subtotals
sort your data by the column(s) that you subtotal on.

Then Data|subtotal and follow the wizard.

You'll see a bunch of =subtotal() formulas inserted (and new lines at each
break). An the last subtotal will be your Grandtotal.

And you'll see some outlining symbols to the left that allow you to hide/show
details.

===
And there's another option you want to learn.

remove the subtotals
Add those headers
Data|Pivottable

You can get just the subtotals without any sorting.

 

[David McRitchie]

Hi Tina,
You should make the sample as small as possible.
Paul and Peo already gave you the answer, but you
posted showing a formula that essentially provides
="" which is text instead of a zero. If you are going to add individual
cells then you must have a zero.

A1: 1
A2: ="" <--- that is a text cell =ISTEXT(A2) would show TRUE_
A3: 4
A4: =A1+A2+A3

-- you can test each of these yourself, but it comes down to what Peo and Paul said.
as above you get #VALUE!
change the A2 to 0 and you are okay
empty the A2 cell with the delete key and you are okay
place a space in the A2 cell and you get #VALUE!
change the A4 to =SUM(A1:OFFSET(A4,-1,0) and you are okay.
clear out A2 to make it an empty cell with the original A4 and you Get #VALUE!
change the A4 to =SUM(A1:A4) and you are okay, but will have
to change the formula manually if you insert a row between row 3 and row4.

="" would equate to text. i.e. =ISTEXT(A2) would show TRUE

Thanks for that, I am trying to add the following:

A B C
1 . 10 10 10
2. 10 10 10
3. 20 20 20 Sub Total
4.
5.
6.
7.
8. Sub Total
9.
10.20 20 20. Total (This is what should be displayed)

This is a much smaller version I am trying to add about 30 subtotals.

The blank cells to have an if statement in which did insert a blank ie. " ", but I have changed this as suggested to "", but this

still returns the same answer of #value!, is it possible to get round it by adding A3+A9+A15 etc etc?