A fix for counting empty cells!

[ronofcam]

Hi all,

I couldn't see a fix for this after doing a few forum searches, so I'm hoping you fine people can help.

In column T of this spreadsheet I have put a formula in [ =(COUNTA($E6,$H6,$K6,$N6,$Q6)-COUNTIF(E6:S6,0))/COUNTA($E6,$H6,$K6,$N6,$Q6) ] which counts the amount of times text is populated in cells E6, H6, K6, N6 and Q6. Or, in other words, how many times someone's name is put in these cells.

Easy enough so far.

I've then asked the formula to express amount of 'Yes' answers as a percentage in T6 and T7 (100% and 75% respectively) by using the COUNTIF and COUNTA functions.

Again, easy enough.

Here's the question I need to answer: How do I stop the rest of the cells displaying a #DIV/0! error message when there's nothing to count in columns E, H, K, N and Q? Ideally I'd like the cells just to stay blank until text is inputted in the respective row, but my Excel knowledge isn't up to it.

Thanks in advance guys.

 

[Joe4]

How do I stop the rest of the cells displaying a #DIV/0! error message when there's nothing to count in columns E, H, K, N and Q?

Wrap the formul in an IFERROR formula, in which you tell it what to display in case of error, i.e.
=IFERROR(your formula here, "")

So this tells it to return an empty string if the formula results in an error (otherwise, it will return the result of the formula).