# 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!!  Register To Reply

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  Register To Reply

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

Brilliant! Thanks!  Register To Reply

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...  Register To Reply

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  Register To Reply

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).

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")))  Register To Reply

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  Register To Reply