Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Dec 2009
    Location
    San Mateo, California
    Posts
    9
    Thanks
    1
    Thanked 0 Times in 0 Posts

    COUNTIF but if Hi-Lited Don't

    What I am trying to do use a statement similar to =COUNTIF(A1:A10,"X") and it will count the x's in the column. What I want to do is if the row is hi-lited I want to ignore the count, so if the count is 5 and one row is hi-lited that has an X then I want the result to be 4. Not sure if this can be done without using VBA which I know nothing about.

    Thanks, Dave
    Last edited by JOB-Dave; 2013-04-12 at 21:39.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Dave,

    AFAIK you can't do it w/o VBA. Sorry.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    Dave,

    Here is what is called a user defined function. It is code written to perform a task behind the scenes but to the user, it is accessed like any other worksheet function. In the following image, you can see a series of values in cells B1 through B9. Cell A1 has the formula =Ccolor(B1:B9,"X") very similar to your Countif formula in your post. It will count all the X's in the range but will ignore those with a yellow background. The value will be returned to the cell with the formula, again, just like an Excel function. You can use the function as many times as you like!

    But entering this formula will not access an Excel built-in formula rather, it will access the user defined formula we built. The synatx is:
    =Ccolor(range, criteria)

    Ccolor.jpg

    Here is what the code looks like:
    Code:
    Public Function Ccolor(rng As Range, reference As Variant) As Integer
    Application.Volatile
    Dim cell As Range
    Dim val As Integer
    val = 0
    For Each cell In rng
    If cell.Interior.Color <> vbYellow And cell.Value = reference Then    'CHANGE THE COLOR IN THIS LINE
        val = val + 1
    End If
    Next cell
    Ccolor = val
    End Function
    So you don't have to know how the code works. You just have to know how to enter the formula in a cell. So where does the code get placed? You have 2 choices:
    1. Build from the attached blank spreadsheet that already has the user defined function in place. The function will be accessible by writing the formula into a cell (see above image)

    OR

    2. Using your existing workbook, press Alt-F11 and the Vb Editor will open. On the menu bar above click Insert>Module. A white screen will appear. Paste this code into the screen then "X" out of the editor. Sound easy? It is!

    VBeditor.jpg

    The code will ignore a yellow interior but you can easily change the color (while in the VB Editor) by changing the vbYellow to:
    color.jpg

    This is a very low impact intorduction to VBA. Might as well get your feet wet!
    HTH,
    Maud
    Attached Files Attached Files
    Last edited by Maudibe; 2013-04-12 at 23:32.

  4. The Following 2 Users Say Thank You to Maudibe For This Useful Post:

    JOB-Dave (2013-04-13),kered (2013-07-31)

  5. #4
    Star Lounger
    Join Date
    Oct 2012
    Posts
    55
    Thanks
    1
    Thanked 10 Times in 10 Posts
    Hi JOB-Dave

    Is there conditional formatting involved! If so you can use that to achieve the count.

  6. #5
    New Lounger
    Join Date
    Dec 2009
    Location
    San Mateo, California
    Posts
    9
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Hi Maud,
    That works exactly the way I want it to, PERFECT!! Thank You so much!

    Dave
    Last edited by JOB-Dave; 2013-04-13 at 11:56.

  7. #6
    Star Lounger
    Join Date
    Oct 2012
    Posts
    55
    Thanks
    1
    Thanked 10 Times in 10 Posts
    I know you guys on here are not bothered about cross posting!

    But this guy (JOB-Dave) has even used the code in post #3 and not even given the credit to Maudibe.

    http://www.excelforum.com/excel-form...ited-dont.html

  8. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    Thanks Kevin for link and the support. Just the fact that Dave acknowledged in another forum that the code is successful to meet his needs make me feel good. Even if someone were to take credit for my work, which Dave did not, I would consider it a form of flattery. I don't see any mal intent and there are no bad feelings here. I am sure others might feel differently about it but I try not sweat the little stuff. Good luck with your project, Dave.

    Maud
    Last edited by Maudibe; 2013-04-14 at 14:46.

  9. #8
    New Lounger
    Join Date
    Jul 2013
    Posts
    2
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Maudibe View Post

    The code will ignore a yellow interior...

    This is a very low impact intorduction to VBA. Might as well get your feet wet!
    HTH,
    Maud
    Would you be able to help me so I can do this same thing with a sum of numbers. Instead of counting x's I just want it to do a sum and exclude numbers highlighted. I tried to use the above process but just get a #Value error

  10. #9
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    Kered,

    In the cell you want the total, enter the formula with the format: =Ccolor(range)
    See the image. You can use currency, decimals, and percentages. Strings will be filtered.

    Note: If you change the background color of a precedent cell, the resulting value will not update until you make a change to any cell.

    HTH,
    Maud

    kered.png


    Code:
    Public Function Ccolor(rng As Range) As Variant
    Application.Volatile
    Dim cell As Range
    Dim val As Variant
    val = 0
    For Each cell In rng
    If cell.Interior.Color <> vbYellow And VarType(cell.Value) <> vbString Then    'CHANGE THE COLOR IN THIS LINE
       val = val + cell.Value
    End If
    Next cell
    Ccolor = val
    End Function
    Last edited by Maudibe; 2013-07-31 at 18:01.

  11. #10
    New Lounger
    Join Date
    Jul 2013
    Posts
    2
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Awesome. I'll give it a shot. Thanks for the great insight.

Posting Permissions

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