Does anyone know a way to manipulate the countif formula to count how many cells are "green" or "red" (with varying text in each cell), rather than the value of the cells?
It can be created an UserDefineFunction that will do this, but for this U should konow the indexnumber of the color or use a cell as sample
Depending on this the formula will differe!
I don't know about using the CountIf for the procedure. I wrote a simple VB script that would do it though. See attached, macro is saved on the workbook.
I don't know about using the CountIf for the procedure. I wrote a simple VB script that would do it though. See attached, macro is saved on the workbook.
If U decide to use an UDF, use something like this
Function CountIfColor(Rangee As Range, Criteria)
countt = 0
Cont = Criteria.Font.ColorIndex
For Each c In Rangee
If c.Font.ColorIndex = Cont Then countt = countt + 1
Next
CountIfColor = countt
End Function
in the first field U must select the Range of cell for wich U wanna perform countif,
in the second field select the sample cell, that must have the color U wanna to count for.
Option Explicit
Function CdlColorCountIf(SearchArea As Range, CdlBgColor As Integer) As Integer
'http://groups.google.com/group/microsoft.public.excel.programming/msg/ac1d98d2c4fbd4a6?ic=1%20
Application.Volatile
Dim CountMe As Boolean, Cnums As Long
Dim x As Long, FmTemp As String
Dim ToCheck As FormatCondition
Dim Cell As Range, Fmla1, Fmla2, FmSwitch
For Each Cell In SearchArea
Cnums = Cell.FormatConditions.Count
If Cnums Then
Set ToCheck = Nothing
For x = 1 To Cnums
If Cell.FormatConditions(x).Interior.ColorIndex = CdlBgColor Then
Set ToCheck = Cell.FormatConditions(x)
Exit For
End If
Next x
If Not ToCheck Is Nothing Then
CountMe = False
FmTemp = ToCheck.Formula1
If Asc(FmTemp) = 61 Then
Fmla1 = Evaluate(FmTemp)
Else
Fmla1 = Val(FmTemp)
End If
If ToCheck.Type = xlExpression Then
CountMe = Fmla1
Else
If ToCheck.Operator <= 2 Then
FmTemp = ToCheck.Formula2
If Asc(FmTemp) = 61 Then
Fmla2 = Evaluate(FmTemp)
Else
Fmla2 = Val(FmTemp)
End If
If Fmla1 > Fmla2 Then
FmSwitch = Fmla1
Fmla1 = Fmla2
Fmla2 = FmSwitch
End If
End If
CountMe = Choose(ToCheck.Operator, Cell >= Fmla1 And Cell <= Fmla2, _
Cell < Fmla1 Or Cell > Fmla2, Cell = Fmla1, Cell <> Fmla1, _
Cell > Fmla1, Cell < Fmla1, Cell >= Fmla1, Cell <= Fmla1)
End If
If CountMe Then CdlColorCountIf = CdlColorCountIf + 1
End If
End If
Next Cell
End Function
I ended up creating another column, and entering R for red, Y for yellow, G for green, and then did a countif based on that column.
Status
Not open for further replies.
You have insufficient privileges to reply here.
Related Threads
?
?
?
?
?
Tech Support Guy
9.9M posts
859.7K members
Since 1998
A forum community dedicated to tech experts and enthusiasts. Come join the discussion about articles, computer security, Mac, Microsoft, Linux, hardware, networking, gaming, reviews, accessories, and more!