+ Reply to Thread
Results 1 to 8 of 8

Use of "blank" cell, "0" and "N/A" in a Formula

  1. 08-08-2015, 10:45 PM #1
    Laurent53
    Laurent53 is offline
    Registered User
    Join Date
    07-21-2015
    Location
    Toronto
    MS-Off Ver
    2010
    Posts
    19

    Use of "blank" cell, "0" and "N/A" in a Formula

    Hello Everyone,

    Hope you are doing well. I try to figure out a formula that will display a total of a range from A2:A15 in A16. What i would like is, if:

    - the range is blank, a "0" is display in A16;
    - the range is equal to "0", a "N/A" is display in A16;
    - the range has values, the total is display in A16.

    I might sound easy for some of you but I can't find out for now Thanks for your help.

    Laurent
    Reply With QuoteRegister To Reply
  2. 08-08-2015, 10:55 PM #2
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,048

    Re: Use of "blank" cell, "0" and "N/A" in a Formula

    What are the contents of that range? formulas or data?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  3. 08-08-2015, 10:58 PM #3
    Laurent53
    Laurent53 is offline
    Registered User
    Join Date
    07-21-2015
    Location
    Toronto
    MS-Off Ver
    2010
    Posts
    19

    Re: Use of "blank" cell, "0" and "N/A" in a Formula

    Hey Ford,

    It will be data (Datas written manually monthly). Thanks.
    Reply With QuoteRegister To Reply
  4. 08-09-2015, 12:08 AM #4
    cyiangou
    cyiangou is offline
    Valued Forum Contributor
    Join Date
    09-17-2012
    Location
    Johannesburg, South Africa
    MS-Off Ver
    Excel 2007
    Posts
    454

    Re: Use of "blank" cell, "0" and "N/A" in a Formula

    Try this in A16:

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  5. 08-09-2015, 08:47 AM #5
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Use of "blank" cell, "0" and "N/A" in a Formula

    I think this does what you want:

    =IF(COUNTA(A2:A15),IF(SUM(A2:A15),SUM(A2:A15),"N/A"),0)
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.
    Reply With QuoteRegister To Reply
  6. 08-10-2015, 10:02 AM #6
    Laurent53
    Laurent53 is offline
    Registered User
    Join Date
    07-21-2015
    Location
    Toronto
    MS-Off Ver
    2010
    Posts
    19

    Re: Use of "blank" cell, "0" and "N/A" in a Formula

    Thank you gentlemen for your help! Both of your formulas work well! This forum is awesome! Thank you again! Laurent
    Reply With QuoteRegister To Reply
  7. 08-10-2015, 10:21 AM #7
    cyiangou
    cyiangou is offline
    Valued Forum Contributor
    Join Date
    09-17-2012
    Location
    Johannesburg, South Africa
    MS-Off Ver
    Excel 2007
    Posts
    454

    Re: Use of "blank" cell, "0" and "N/A" in a Formula

    My pleasure, thank you.
    Reply With QuoteRegister To Reply
  8. 08-10-2015, 10:49 AM #8
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Use of "blank" cell, "0" and "N/A" in a Formula

    You're welcome. We appreciate the feedback!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Don't know the Forumlas with problem either using "vlookup" "if" "and" or "index" "match"
    By Jokeman in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-17-2013, 12:05 PM
  2. [SOLVED] How to USE """"" cells count """"" change font color
    By austin123456 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-09-2013, 06:14 AM
  3. solution for the blank cell using the IF function
    By ragnaedge in forum Excel General
    Replies: 4
    Last Post: 08-23-2013, 11:03 AM
  4. Excel 2007 : Match Values and Return "Present" "Absent" "Per Diem" or remain blank
    By DanBraden in forum Excel General
    Replies: 3
    Last Post: 02-16-2011, 02:55 PM
  5. Formula returning result of "True" or "False" instead of "1" or "0"
    By AllenMead in forum Excel General
    Replies: 5
    Last Post: 10-12-2010, 06:46 AM
  6. if statement see "Male" or "Female" returning "Y" otherwise leave blank
    By qwervbnmasdf in forum Excel General
    Replies: 3
    Last Post: 12-14-2006, 01:36 PM
  7. Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next
    By BCB in forum Excel - New Users/Basics
    Replies: 7
    Last Post: 05-13-2006, 05:02 PM
  8. If changed array formula reduce ""\""\""\ - signs to #Missing, will it make ...
    By Maria J-son in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-05-2006, 08:25 AM

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1