# Thread: COUNTIF but if Hi-Lited Don't

1. ## 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

2. Dave,

AFAIK you can't do it w/o VBA. Sorry.

3. 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

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

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

5. Hi JOB-Dave

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

6. Hi Maud,
That works exactly the way I want it to, PERFECT!! Thank You so much!

Dave

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

9. Originally Posted by Maudibe

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. 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```

11. 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
•