Return TRUE if the column contains only "D" values ​​or is empty

Question

Which formula will return TRUE if the column contains only cells with a value of "D" or empty?

I need this to control conditional formatting.

Let me briefly tell you how I use it: I save all translation lines for 9 languages ​​for all graphic texts in my games. I need to control the status of all texts, because I often change them, and I need to transfer the changed text to the translation agency. I use column A to control the status of texts stored in column B. State D means Done. I use conditional formatting for green cells made and all other yellow ones. Now your formula helps me highlight the heading cell and tells me if there are cells in this language that are awaiting translation or not.

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

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

Answer 1

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

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

This will count the number of non-empty cells in column X and check if it matches the number of cells in column X equal to "D".

For more complex applications, you can also change the second parameter in COUNTIF to another conditional statement - see the link for some examples.

Answer 2

Here is a solution to the formula:

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

• CountA - the number of cells with data
• CountIf reports how many cells has a "D"
• Just wrap in a simple If statement :)

* Please note that COUNTIF is not case sensitive.

Answer 3

I would put this in cell B1 to check cell A1:

 =OR(A1="D", A1="") 

Depending on your locale, you may need to ; for,, and you may need to use an OR translation. Do not blame me :) Blame Misrosoft

To combine a complete column, do

 =AND(B:B) 

(or perhaps a shorter range: =AND(B1:B8) for example.)

Edit install link from demo: https://docs.google.com/spreadsheet/ccc?key=0AlX1n8WSRNJWdFAwZEpNRGhwdTNucDJ3dWQ1V3owRkE

Edit To comment:

A custom worksheet function for this would look something like this:

 ' untested code - written in browser Public Function HasOnlyD(rng as Range) As Boolean Dim cell As Range For Each cell In rng If (Not cell.Value = "") And (Not cell.Value = "D") Then HasOnlyD = False Exit Function End If Next HasOnlyD = True End Function 

Answer 4

You can also use Conditional Formatting:

• Select a data range (in my case, C5: L10)
• Select formatting using formulas and use =(ROWS(C$5:C$10)=COUNTIF(C$5:C$10;"=D")+COUNTBLANK(C$5:C$10))
• You get this form:

Hope this helps

Hello