Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    496
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Dear Loungers,

    I have acolumn which may contain dates, some of which will be in the future and I want to count them. I thought this would work:

    countif(D537,">now()")
    But nothing is counted, I get a result of zero. I currently have 33 cells in the range, 14 with dates, 11 of those are in the future. Interestingly if I change the formula to look for previous dates by changing the > sign to < I get 19 which is the number of blank cells. How can I make this work?

    Thank you.......................... liz

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Change your formula to"
    =countif(D537,">" & now())

  3. #3
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    496
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Oh! isn't it easy when you know how, I had played with this before but missed the ampersand.

    Thank you

  4. #4
    Star Lounger
    Join Date
    Jun 2002
    Location
    Georgia, U. S. A.
    Posts
    90
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Not exactly related, but I'm wondering if you can count cells that are formatted as %. I tried =countif(G3:G32,Style="Percent") but that didn't work. Any ideas?

  5. #5
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    You'll have to use a custom function to do this.

    Code:
    Function countPercents(myRange As Range) As Long
    Dim myCell As Range
    For Each myCell In myRange
        If Right(myCell.NumberFormat, 1) = "%" Then
        countPercents = countPercents + 1
    End If
    Next
    End Function

  6. #6
    Star Lounger
    Join Date
    Jun 2002
    Location
    Georgia, U. S. A.
    Posts
    90
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Thanks for that solution. I'm not sure where I was reading this the other day on these posts but I thought someone was trying to count cells based on colors. Several people were saying it couldn't be done or it was difficult. Here are a couple functions I wrote several years ago that will do it fairly easily. You just have to put these into the sheet you want to use and then treat them like any of the built in functions that come with Excel. The second function will return the colorIndex number that excel uses for any color. Say you want to know what color is in cell D7. In some other cell, you would put =FindColor(D7) and then that cell would have a number in it that is the Color Index used by Excel.

    Function CountColors(R As Range, Col As Integer) As Integer
    Dim cell As Range
    CountColors = 0
    For Each cell In R
    If cell.Interior.ColorIndex = Col Then
    CountColors = CountColors + 1
    End If
    Next cell
    End Function

    Function FindColor(R As Range) As Integer
    FindColor = R.Interior.ColorIndex
    End Function

  7. #7
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I think the difficulty they were referring to is that the colors to be counted were the result of conditional formatting, not standard formatting. Conditional formatting colors do no assign a ColorIndex value to a cell.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •