CODEWITHSUNDEEP
excel
PerfectGamesOnline.com
PerfectGamesOnline.com

Reputation: 1778

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

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.

Upvotes: 4

Views: 5777

Answers (4)

Alen
Alen

Reputation: 1075

Also you can use Conditional formating:

  • Select your range of data (in my case C5:L10)
  • Select formatting with formulae and use this =(ROWS(C$5:C$10)=COUNTIF(C$5:C$10;"=D")+COUNTBLANK(C$5:C$10))
  • You get a formating like this: The column is formated only it have "D" or empty cells

Hope this helps

Regards

Upvotes: 0

Gaijinhunter
Gaijinhunter

Reputation: 14685

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

Upvotes: 3

sehe
sehe

Reputation: 394044

I'd put this in cell B1 to check cell A1:

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

Depending on your locale setting, you might need to ; for , and you might need to use your language's translation of OR. Don't blame me :) Blame Misrosoft

To combine a full column, do 

=AND(B:B)

(or perhaps a shorter range: =AND(B1:B8) e.g.)

Edit put up a link with a demo: https://docs.google.com/spreadsheet/ccc?key=0AlX1n8WSRNJWdFAwZEpNRGhwdTNucDJ3dWQ1V3owRkE

Edit To the comment:

A userdefined worksheet function to do this would roughly look like:

' 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

Upvotes: 2

Mischinab
Mischinab

Reputation: 2921

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.

Upvotes: 4

Related Questions