How to convert every text in a column to zero when summed?

[molosser]

Ediit:

I'm so sorry, I just realized my question was totally wrong.
It supposed to be how to get the total value without coming out with an error?
Total Value B3+B7

Suppose we're involving lots of data that contains texts and numbers and sums.

----

Hi, please advise me what is the proper way to do this.
Basically so that the all the texts are read as zero so as to avoid error.

	A	B
1	value A	-
2	Value B	-
3	Sum 1	Sum B1:B2
4
5	Value C	-
6	Value D	100
7	Sum 2	Sum B5:B6
8
9	Total Vakue	B3+B7

[MrShorty]

The built in SUM() function already ignores text, so I would expect to use =SUM(B1:B2) and =SUM(B5:B6) in B3 and B7. =B3+B7 or =SUM(B3,B7) should have no trouble summing those two results in B9.

[molosser]

The built in SUM() function already ignores text, so I would expect to use =SUM(B1:B2) and =SUM(B5:B6) in B3 and B7. =B3+B7 or =SUM(B3,B7) should have no trouble summing those two results in B9.

I'm so sorry, I just realized my question was totally wrong.
It supposed to be how to get the total value without coming out with an error?
Total Value B3+B7

Suppose we're involving lots of data that contains texts and numbers and sums.

[MrShorty]

Do you have an example data set where you get an error with those functions? Based on the limited information given, I would say to avoid use of the + operator, since the + operator doesn't know how to cope with text, but the SUM() function has no trouble ignoring text.

[molosser]

Do you have an example data set where you get an error with those functions? Based on the limited information given, I would say to avoid use of the + operator, since the + operator doesn't know how to cope with text, but the SUM() function has no trouble ignoring text.

I don't have an example data set with me at the moment it's similar to the data in OP.
Suppose there are many Sums: Sum 1, Sum 2, Sum 3 etc.

The formula =SUM(B3,B7) works perfectly.
But =B3+B7 comes out #VALUE!

But just to cover my base.
What if Total value is Sum 1 - Sum 2, or Sum 1 x Sum 2, or Sum 1 : Sum 2?
What are the appropriate formulas for these?

[Glenn Kennedy]

Please upload a sample excel file, complete with expected answers... calculated manually.

[MrShorty]

I'm still not sure exactly what you are trying to do. A few more thoughts:

Are you required to put the intermediate summary values in column B? I usually find that I prefer to have the intermediate summary values in their own column. I would generally prefer =SUM(B1:B2) etc. in column C. C3=SUM(B1:B2), C7=SUM(B5:B6) and so on. Then my final total formula can be =SUM(C1:C100) (or whatever range is needed). If one of the subtotals is supposed to be subtracted rather than added in the final total, subtract it in it's cell =-SUM(B5:B6).

For multiplication and division, use the PRODUCT() function, in the same manner.

Is that helpful?

[molosser]

I'm still not sure exactly what you are trying to do. A few more thoughts:

Are you required to put the intermediate summary values in column B? I usually find that I prefer to have the intermediate summary values in their own column. I would generally prefer =SUM(B1:B2) etc. in column C. C3=SUM(B1:B2), C7=SUM(B5:B6) and so on. Then my final total formula can be =SUM(C1:C100) (or whatever range is needed). If one of the subtotals is supposed to be subtracted rather than added in the final total, subtract it in it's cell =-SUM(B5:B6).

For multiplication and division, use the PRODUCT() function, in the same manner.

Is that helpful?

Yes, it's very helpful.
Thank you very much for the kind help, problem solved.