+ Reply to Thread
Results 1 to 7 of 7

Countifs should return blank results for blank row of cells

  1. #1
    Forum Contributor
    Join Date
    07-29-2013
    Location
    Oshawa
    MS-Off Ver
    Excel 2010
    Posts
    660

    Countifs should return blank results for blank row of cells

    Hi everyone,

    I'm using Excel 2010 and I'm hoping to get some help. I have a formula which will count the number of N's and Y's and derive a Pass/Fail result from them. However, as it stands if the entire row is blank the formula gives me a Pass result.

    Here is my formula:

    =IF(AND(G5="N/T",H5="N/T",I5="N/T",J5="N/T",K5="N/T"),"N/T",IF(COUNTIFS(G5:K5,"N"),"Fail","Pass"))

    What can I do to make sure no result is show (a blank results cell) if the entire row is blank?

    Thanks everyone!!

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    21,264

    Re: Countifs should return blank results for blank row of cells

    Try this approach:

    =IF(COUNTA(G5:K5)=0,"",IF(AND(G5="N/T",H5="N/T",I5="N/T",J5="N/T",K5="N/T"),"N/T",IF(COUNTIFS(G5:K5,"N"),"Fail","Pass")))

    Hope this helps.

    Pete

    EDIT: You can also simplify it further:

    =IF(COUNTA(G5:K5)=0,"",IF(COUNTIF(G5:K5"N/T")=5,"N/T",IF(COUNTIF(G5:K5,"N"),"Fail","Pass")))

    HTH

    Pete
    Last edited by Pete_UK; 10-29-2013 at 03:34 PM.

  3. #3
    Forum Contributor
    Join Date
    07-29-2013
    Location
    Oshawa
    MS-Off Ver
    Excel 2010
    Posts
    660

    Re: Countifs should return blank results for blank row of cells

    Brilliant! Thanks!

  4. #4
    Valued Forum Contributor
    Join Date
    09-10-2012
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2010
    Posts
    929

    Re: Countifs should return blank results for blank row of cells

    I'd stick with your formula... then change the number format to #,##0;;;

    This will force positives to show, negative to not show, zeros to not show, text to not show...

  5. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    21,264

    Re: Countifs should return blank results for blank row of cells

    Quote Originally Posted by djapigo View Post
    This will force positives to show, negative to not show, zeros to not show, text to not show...
    But he wants texts to show ...

    Pete

  6. #6
    Registered User
    Join Date
    10-18-2013
    Location
    Atlanta, GA, USA
    MS-Off Ver
    Excel 2016
    Posts
    13

    Re: Countifs should return blank results for blank row of cells

    Your formula returns 'Pass' if all cells are blank because you told it to return 'Pass' if G5:K5 was not 'N' (Blank=Not N).

    Add

    IF(AND(G5="",H5="",I5="",J5="",K5=""),"", to the beginning of your formula. This will weed out the blanks before looking for the 'N/T' and 'N'. The complete formula will look like:

    =IF(AND(G5="",H5="",I5="",J5="",K5=""),"",IF(AND(G5="N/T",H5="N/T",I5="N/T",J5="N/T",K5="N/T"),"N/T",IF(COUNTIFS(G5:K5,"N"),"Fail","Pass")))

  7. #7
    Valued Forum Contributor
    Join Date
    09-10-2012
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2010
    Posts
    929

    Re: Countifs should return blank results for blank row of cells

    To show the text... you should have the number format as #,##0;;;@

    positives are formatted as #,##0
    negatives will not show
    zeros will not show
    text will be shown as general text

    Custom number formatting is broken down as 4 types (separated by a semicolons) where positive;negative;zero;text

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Return non-blank cells from range AND ignore cells with only formula w/o results
    By grafitti in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-06-2014, 08:15 AM
  2. Return Blank Cell if Sum Cells Blank or Zero
    By Battledeck in forum Excel General
    Replies: 6
    Last Post: 06-11-2012, 05:55 PM
  3. Trying to return Linked Cells blank when blank.
    By RAMOORE in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-08-2011, 02:55 PM
  4. Replies: 3
    Last Post: 11-22-2007, 07:53 AM
  5. [SOLVED] referencing cells that return blank results
    By Suz in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-21-2005, 07:06 PM

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