# Countifs should return blank results for blank row of cells

1. ## 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. ## 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

3. ## Re: Countifs should return blank results for blank row of cells

Brilliant! Thanks!

4. ## 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. ## Re: Countifs should return blank results for blank row of cells

Originally Posted by djapigo
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. ## 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. ## 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

#### Thread Information

##### Users Browsing this Thread

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

#### 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