1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: countif based on cell format, not value?

Discussion in 'Business Applications' started by gurutech, Oct 18, 2007.

Thread Status:
Not open for further replies.
Advertisement
  1. gurutech

    gurutech Thread Starter

    Joined:
    Apr 23, 2004
    Messages:
    2,960
    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?

    Can this be done in VB?
     
  2. Aj_old

    Aj_old

    Joined:
    Sep 24, 2007
    Messages:
    869
    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!
     
  3. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
  4. WAJ0606

    WAJ0606

    Joined:
    Oct 10, 2007
    Messages:
    137
    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.
     

    Attached Files:

  5. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    You're better off using a UDF instead of how you're doing it, as aj_old suggests. (y)
     
  6. Aj_old

    Aj_old

    Joined:
    Sep 24, 2007
    Messages:
    869
    If U decide to use an UDF, use something like this
    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.

    GoodLuck :cool:
     
  7. Aj_old

    Aj_old

    Joined:
    Sep 24, 2007
    Messages:
    869
    for more info see the file
     

    Attached Files:

  8. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Probably better off with another UDF actually...

    Edit: Hmm, part of post was cut off.. Look here for standard fill colors..
    http://www.mrexcel.com/archive2/38500/44608.htm
    http://www.cpearson.com/Excel/colors.htm

    .. below is for conditional format colors..
    Code:
    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
    For more information on colors, check this out...

    http://www.geocities.com/davemcritchie/excel/colors.htm#ColorFormulas

    The only non-VBA way (AFAIK) is what I posted earlier.

    HTH
     
  9. gurutech

    gurutech Thread Starter

    Joined:
    Apr 23, 2004
    Messages:
    2,960
    Thanks everyone for the suggestions!

    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.
     
  10. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Similar Threads - Solved countif based
  1. mtf
    Replies:
    1
    Views:
    183
  2. jeannier1975
    Replies:
    2
    Views:
    280
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/639987

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice