Excel Function to exclude count of cells that has a different fill color

I need a code to count only the cells in a worksheet that:

a) is equal or greater than 75
AND
b) exclude those that has a fill color of gray
Please see attached worksheet as sample.

Thanks in advance....
Sample-Test-for-Count.xls
emacyamAsked:
Who is Participating?
 
dlmilleConnect With a Mentor Commented:
Ok this one has three parameters

function(range, must be greater than or equal to number, and colorindex for exclusion)

e.g.,  =CountSkipColor(A1:A5,75,15) - checks range A1-A5, must be >= 75 to count, and must not be colorindex 15 (grey)
Function CountSkipColor(rRange As Range, minOver As Double, colorToCheck As Double) As Long
Dim myCell As Range, countcolor As Long

Application.Volatile
    For Each myCell In rRange
        If myCell.Interior.ColorIndex <> colorToCheck And myCell.Value >= minOver And IsNumeric(myCell.Value) Then 'not grey and over minimum amount
            countcolor = countcolor + 1
        End If
        
    Next myCell
    
    CountSkipColor = countcolor
End Function

Open in new window


PS - also added the function getColorIndex(range) returns the color index of a cell you point to (or first cell in a range you point to) so that should help for future....
 
Function getColorIndex(rRange As Range) As Long

    getColorIndex = rRange.Cells(1, 1).Interior.ColorIndex
End Function

Open in new window

Enjoy!

Dave
Sample-Test-for-Count-r3.xls
0
 
SiddharthRoutConnect With a Mentor Commented:
Here is a sample.

Code Used

Public Function MyTot(rng As Range) As Long
    Application.ScreenUpdating = False
    Application.Volatile
    Dim cl As Range, tot As Long
    
    For Each cl In rng
        If IsNumeric(cl.Value) Then
            If cl.Interior.ColorIndex <> 15 And (cl.Value = 75 Or cl.Value > 75) _
            Then tot = tol + 1
        End If
    Next
    MyTot = tot
    Application.ScreenUpdating = True
End Function

Open in new window

Total-Example.xls
0
 
emacyamAuthor Commented:
@SiddharthRout:  Thanks for the code but when I do it manually, the count should be 4.  ~thanks.

0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
dlmilleCommented:

Also, attached solution worksheet:

Enjoy!

Dave
Function CountSkipGrey(rRange As Range, minOver) As Long
Dim myCell As Range, countcolor As Long


    For Each myCell In rRange
        Debug.Print myCell.Value, myCell.Interior.ColorIndex
        If myCell.Interior.ColorIndex <> 15 And myCell.Value >= minOver Then 'not grey and over minimum amount
            countcolor = countcolor + 1
        End If
        
    Next myCell
    
    CountSkipGrey = countcolor
End Function

Open in new window

Sample-Test-for-Count-r1.xls
0
 
dlmilleCommented:
@sid - didn't know you posted and I was too slow (I was interrupted !) lol

@ emacyam

This one works!!!
Dave
0
 
SiddharthRoutCommented:
Sorry there is a typo

Replace

Then tot = tol + 1

by

Then tot = tot + 1

Sid
0
 
emacyamAuthor Commented:
@Sidd:  The replace quote is the same....  thanks.
0
 
dlmilleCommented:
@emacyam - mine works, dude!

See post 35033149, just above :)

Dave
0
 
SiddharthRoutCommented:
No it is not ;)

the 1st one has tol (notice the L)

Sid
0
 
dlmilleCommented:
@sid - you got me on the isnumeric.  I was "there", but got interrupted then plunged ahead and missed it....

lol - we working too fast between type-o's and interruptions....

Dave

Here's my updated...
Function CountSkipGrey(rRange As Range, minOver) As Long
Dim myCell As Range, countcolor As Long


    For Each myCell In rRange
        If myCell.Interior.ColorIndex <> 15 And myCell.Value >= minOver And IsNumeric(myCell.Value) Then 'not grey and over minimum amount
            countcolor = countcolor + 1
        End If
        
    Next myCell
    
    CountSkipGrey = countcolor
End Function

Open in new window

Sample-Test-for-Count-r2.xls
0
 
emacyamAuthor Commented:
@dlmille:  your solution worked but when I did some tests (add another number greater than 75 in a cell and applied gray fill color), the formula counted that cell.

@Sidd:  thanks, your solution worked.  Thing is, is there a way to recalculate without saving the document first and reopen?  Is there a hot key?

Thanks to both of you :)  
0
 
dlmilleCommented:
Apologies on that.

Try once again!

Dave
Sample-Test-for-Count-r2.xls
0
 
dlmilleCommented:
So the function I created allows you to select a range to count, and a number it can be > or = to to include in the count.

Dave
0
 
dlmilleCommented:
And you have to use the EXACT same color of GREY - we're both keying in on colorindex = 15....

Dave
0
 
SiddharthRoutCommented:
Yes.

You have to calculate the formula again.You can press F2 where the formula is so that you don't need to restart.

Sid
0
 
emacyamAuthor Commented:
GREAT!  Worked like a charm on both solutions :-)  thanks a bunch!

@dlmille:  just to understand your formula, what does the "75" do? =countskipgrey(B2:B35,75)
0
 
dlmilleCommented:
you can change the 75 to any number that marks your MINIMUM.  E.g., GREY is hard coded, but 75 you can change what the bottom limit is for exclusion or inclusion

Dave
0
 
emacyamAuthor Commented:
@dlmille:  cool!  I like that :)
0
 
dlmilleCommented:
Would you like me to add a variable for the COLOR to check as well?

Dave
0
 
SiddharthRoutCommented:
emacyam

you might need this link

http://www.mvps.org/dmcritchie/excel/colors.htm

for the color index ;)

Sid
0
 
emacyamAuthor Commented:
@dlmille:  can you please repost the code from the last sample so I an accept that solution as well....

@sidd and dlmille:  will split the points... hope its ok...
0
 
SiddharthRoutCommented:
It is okay with me ;)

Sid
0
 
dlmilleCommented:
Sounds good to me - coming momentarily
0
 
emacyamAuthor Commented:
@Sidd:  thanks a bunch... that link is great!

@Dave:  why not?  that variable will come in handy for sure :)
0
 
emacyamAuthor Commented:
Both experts were very helpful. A+++ all the way!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.