Math based on text or formatting

[Dave]

Excel 2003

I have 2 needs.

1st
cell range (d5:d75)
How can I COUNT the number of cells in that range that have a specific text
value (ex: "BS in Sci").

Would the same formula work if that range of cells were all drop downs and
the selected drop down value was (ex: "BS in Sci").

2nd
cell range (c5:c75)

How can I count the number of cells in that range that have the text color
formatted "RED"

Thanks much

Dave

 

[Pete_UK]

First one:

=COUNTIF(D575,"BS in Sci")

and yes, this will work if those values are generated from pull-downs.

Second one: You can't use a formula specifically to count formats. If
those cells are coloured by means of conditional formatting, then it
would be possible to set up a formula which counts the cells by the
same criteria, but you need to specify what criteria is being used.

However, if those cells are coloured manually, then you would need a
macro (or user-defined function) to count them.

Hope this helps.

Pete

 

[L. Howard Kittle]

Pete'a formula will take of your first query and maybe this will take care
of the second.

You could use this to count the RED font cells in the range D575 by naming
that range Data. It counts other cell also whose fonts are different colors
but these can be eliminated if you want.

Sub FontColorCount()
'Counts the number of certain colored
'fonts in a range named Data.
Dim cell As Range
Dim Blue5 As Integer, Red3 As Integer, _
Green4 As Integer, Yellow6 As Integer

For Each cell In Range("Data")
If cell.Value <> "" _
And cell.Font.ColorIndex = 5 Then
Blue5 = Blue5 + 1
ElseIf cell.Value <> "" _
And cell.Font.ColorIndex = 3 Then
Red3 = Red3 + 1
ElseIf cell.Value <> "" _
And cell.Font.ColorIndex = 4 Then
Green4 = Green4 + 1
ElseIf cell.Value <> "" _
And cell.Font.ColorIndex = 6 Then
Yellow6 = Yellow6 + 1
End If
Next

Range("A1").Value = Blue5 & " Blue"
Range("A2").Value = Red3 & " Red"
Range("A3").Value = Green4 & " Green"
Range("A4").Value = Yellow6 & " Yellow"

MsgBox " You have: " & vbCr _
& vbCr & " Blue " & Blue5 _
& vbCr & " Red " & Red3 _
& vbCr & " Green " & Green4 _
& vbCr & " Yellow " & Yellow6, _
vbOKOnly, "CountColor"
End Sub

HTH
Regards,
Howard

 

[Dave]

Thank you both.

Exactly what I needed

Dave

Excel 2003

I have 2 needs.

1st
cell range (d5:d75)
How can I COUNT the number of cells in that range that have a specific
text value (ex: "BS in Sci").

Would the same formula work if that range of cells were all drop downs and
the selected drop down value was (ex: "BS in Sci").

2nd
cell range (c5:c75)

How can I count the number of cells in that range that have the text color
formatted "RED"

Thanks much

Dave



__________ Information from ESET NOD32 Antivirus, version of virus
signature database 4712 (20091223) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com

__________ Information from ESET NOD32 Antivirus, version of virus signature database 4712 (20091223) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com