Solved

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

Posted on 2011-03-03
25
353 Views
Last Modified: 2012-06-27
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
0
Comment
Question by:emacyam
  • 11
  • 8
  • 6
25 Comments
 
LVL 30

Assisted Solution

by:SiddharthRout
SiddharthRout earned 250 total points
ID: 35033098
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
 

Author Comment

by:emacyam
ID: 35033137
@SiddharthRout:  Thanks for the code but when I do it manually, the count should be 4.  ~thanks.

0
 
LVL 42

Expert Comment

by:dlmille
ID: 35033149

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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 42

Expert Comment

by:dlmille
ID: 35033157
@sid - didn't know you posted and I was too slow (I was interrupted !) lol

@ emacyam

This one works!!!
Dave
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35033159
Sorry there is a typo

Replace

Then tot = tol + 1

by

Then tot = tot + 1

Sid
0
 

Author Comment

by:emacyam
ID: 35033169
@Sidd:  The replace quote is the same....  thanks.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35033172
@emacyam - mine works, dude!

See post 35033149, just above :)

Dave
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35033174
No it is not ;)

the 1st one has tol (notice the L)

Sid
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35033201
@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
 

Author Comment

by:emacyam
ID: 35033245
@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
 
LVL 42

Expert Comment

by:dlmille
ID: 35033259
Apologies on that.

Try once again!

Dave
Sample-Test-for-Count-r2.xls
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35033266
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
 
LVL 42

Expert Comment

by:dlmille
ID: 35033271
And you have to use the EXACT same color of GREY - we're both keying in on colorindex = 15....

Dave
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35033274
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
 

Author Comment

by:emacyam
ID: 35033286
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
 
LVL 42

Expert Comment

by:dlmille
ID: 35033292
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
 

Author Comment

by:emacyam
ID: 35033295
@dlmille:  cool!  I like that :)
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35033296
Would you like me to add a variable for the COLOR to check as well?

Dave
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35033297
emacyam

you might need this link

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

for the color index ;)

Sid
0
 

Author Comment

by:emacyam
ID: 35033301
@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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35033303
It is okay with me ;)

Sid
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35033304
Sounds good to me - coming momentarily
0
 

Author Comment

by:emacyam
ID: 35033306
@Sidd:  thanks a bunch... that link is great!

@Dave:  why not?  that variable will come in handy for sure :)
0
 
LVL 42

Accepted Solution

by:
dlmille earned 250 total points
ID: 35033321
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
 

Author Closing Comment

by:emacyam
ID: 35033335
Both experts were very helpful. A+++ all the way!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Macro to change number to Month in Excel? 10 45
Converting ounces to grams - simple formula 8 40
Excel formula - data format 5 17
Index Match Formula VBA 6 13
Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question