return TRUE if a column contains only values "D" or empty

Question

What formula would return TRUE if a column contains only cells with value "D" or empty?

I need this to control the conditional formatting.

Let me briefly tell you how I use it: I keep all translation strings for 9 languages for all GUI texts in my games. I need to control the status of all the texts because I change them frequently and I need to give the changed text out to translation agency. I use column A to control the status of texts stored in column B. Status D means Done. I use conditional formatting to green the done cells and yellow all the other ones. Now, your formula helps me to highlight the header cell and tells me whether this language has cells waiting for translation or not.

I use this principle for all my text data where I need to track the status. I have macros for setting the conditional formatting of column B based on values in column A if anyone is interested. In my macro is obviously column A the column with the active cell ;-)

EDIT: inspired by the answers below, my final formula is =(COUNTA(R:R))<>COUNTIF(R:R;"D")+1 where the +1 is for skipping the heading.

Answer 1

You can do this by counting the number of "D"s and comparing this against the total number of non-blank cells:

=(COUNTA(X:X)=COUNTIF(X:X,"D"))

This will count the number of non-blank cells in column X, and check whether it is the same as the number of cells in column X that equal "D".

For more complicated uses, you can also modify the second parameter in the COUNTIF to some other conditional statement - see this link for some examples.

Answer 2

Here is the formula solution:

=IF(COUNTA(A:A)=COUNTIF(A:A,"D"),TRUE,FALSE)

• CountA is number of cells with data
• CountIf tells you how many cells have "D"
• Just wrap in a simple If statement :)

*Please note that COUNTIF is not case-sensitive