+ Reply to Thread
Results 1 to 12 of 12

COUNT cells and IF ALL cells are blank do not display 0

  1. #1
    Registered User
    Join Date
    05-24-2013
    Location
    Aktobe
    MS-Off Ver
    Excel 2010
    Posts
    20

    Question COUNT cells and IF ALL cells are blank do not display 0

    Dear Gurus! Your assistance please on the below....

    I need to count cells with numbers only ignoring any text but if ALL CELLS within the range are blank or contain text only then i do not want the formula to display 0. it needs just to leave it blank.

    Appreciate your assistance!
    Last edited by Abu Abdurakhim; 05-27-2013 at 09:52 AM.

  2. #2
    Forum Contributor
    Join Date
    04-23-2013
    Location
    Abu Dhabi United Arab Emirates
    MS-Off Ver
    Excel 2010
    Posts
    178

    Re: COUNT cells and IF ALL cells are blank do not display 0

    try using subtotal():
    SUBTOTAL(2,A1:A10)
    where 2 - counting only numbers

  3. #3
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: COUNT cells and IF ALL cells are blank do not display 0

    Maybe something like this...

    =IFERROR(1/(1/COUNT(A2:A10)),"")

    Adjust the range to suit.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  4. #4
    Registered User
    Join Date
    05-24-2013
    Location
    Aktobe
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: COUNT cells and IF ALL cells are blank do not display 0

    And it will not display 0? Thanks a lot!!!

  5. #5
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: COUNT cells and IF ALL cells are blank do not display 0

    Quote Originally Posted by Abu Abdurakhim View Post
    And it will not display 0?
    Nope. No zeros!

    Thanks a lot!!!
    You're welcome!

  6. #6
    Registered User
    Join Date
    05-24-2013
    Location
    Aktobe
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: COUNT cells and IF ALL cells are blank do not display 0

    It does work but i need it also to show zero when all cells in the range contain text only.
    For example if in the past months Jan, Feb, Mar and Apr there were no activities i input "nothing reported" and it should show 0, but when we talk about future months like June, July the cells are blank (no text and no figures) it should show blank cell without zero. As soon as data entered, if numbers it will count cells, and if text only it will show 0. Is it possible? Please, find attached excel file with an example. Thanks a lot!
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    05-24-2013
    Location
    Aktobe
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: COUNT cells and IF ALL cells are blank do not display 0

    Any help please on the above? Cheers!

  8. #8
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: COUNT cells and IF ALL cells are blank do not display 0

    In the sample file there are 3 rows allotted for each month. Will there always be only 3 rows of data per month?

  9. #9
    Registered User
    Join Date
    05-24-2013
    Location
    Aktobe
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: COUNT cells and IF ALL cells are blank do not display 0

    No it may go up to 5 maximum. Thanks!!

  10. #10
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: COUNT cells and IF ALL cells are blank do not display 0

    Try this...

    =IF(COUNTA(B3:B5)=0,"",IF(COUNT(B3:B5),COUNT(B3:B5),0))

  11. #11
    Registered User
    Join Date
    05-24-2013
    Location
    Aktobe
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: COUNT cells and IF ALL cells are blank do not display 0

    It works fantastic! Thanks a lot!!!! Highly appreciated!

  12. #12
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: COUNT cells and IF ALL cells are blank do not display 0

    You're welcome. Thanks for the feedback!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1