Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Doorn, Netherlands
    Posts
    311
    Thanks
    0
    Thanked 0 Times in 0 Posts
    How to add cells containing text or numbers (not all cells containing text)? If there is text, the contents should be considerd 0 (zero) in numerical terms.

    For example, suppose A1 contains "-", a hyphen as a result of a calculation, indicating there is no numerical result, and cell B1 containing, say, 4 (the number).

    How then to add A1 and B1, resulting in 4, i.e. inherently stating the "-" in A1 is a 0 (zero)?

    If the result of the formula in A1 yields 3 instead of "-", A1 +B1 would yield 3+4 =7.

    Any ideas are welcome.

    I know that I can set the spreadsheet to show all zeroes as -, but that is not possible in my case.

    Regards, Teunis

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You can just use SUM:
    =SUM(A1,B1)
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Doorn, Netherlands
    Posts
    311
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='rory' post='797677' date='13-Oct-2009 13:19']You can just use SUM:
    =SUM(A1,B1)[/quote]

    Brilliant! No difficult IFs, etc.
    Thanks a lot,

    Teunis

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    And, of course, if you want to sum more cells, you can use

    =SUM(A1:H1)

    instead of

    =SUM(A1,B1,C1,D1,E1,F1,G1,H1)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •