A fix for counting empty cells!

ronofcam

New Member
Joined
Oct 30, 2013
Messages
8
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.
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,118
Office Version
365
Platform
Windows
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).
 
Last edited:

Forum statistics

Threads
1,089,646
Messages
5,409,502
Members
403,266
Latest member
HMR120

This Week's Hot Topics

Top