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

Brilliant! Thanks!

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

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

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

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