|
|
|
|
New Member
|
|
Apr 7, 2004, 10:16 AM
|
|
Excel count function
I am trying to count a large group of cells that are colored depending on the data. For example: I want to count the number of red cells which are faults. I tried the countif but not sure how to set the criteria.
Thanks for any help.
|
|
|
Senior Member
|
|
Apr 7, 2004, 03:03 PM
|
|
Re: Excel count function
Here's the basic syntax
=countif(cells, condition)
so you would take whatever you are calculating as a fault and put that under the conditions part (for example, <5). The cells are whichever ones you are trying to count with (ie. a1:e5). Assign this to a cell being sure to have an = at the beginnging to show that it is a calculation, and that cell will show the number of cells in a1:e5 that are <5.
|
|
|
New Member
|
|
Apr 8, 2004, 06:22 AM
|
|
Excel count function
Thanks for your response. I got that far but I want to set the condition as the color, not a number or text in the cell. I have a workbook with multiple cells filled in with different colors. I want to count certain color of cells.
|
|
|
New Member
|
|
May 6, 2004, 11:57 PM
|
|
Excel count function
Hi, fyogi1.
This requires a user-defined function (UDF). Check out Chip Pearson's site:
http://www.cpearson.com/excel/colors.htm
I'm sure it's a bit late, but you'll have it for next time.
|
|
|
New Member
|
|
Nov 18, 2004, 02:53 PM
|
|
Try this code
Create this function:
Function CountCol(SumRange As Range, intColor As Integer) As Integer
Dim I As Integer
Dim Cell As Range
Set SumRange = SumRange.SpecialCells(xlCellTypeAllFormatCondition s)
For Each Cell In SumRange
If Cell.Interior.ColorIndex = intColor Then
I = I + 1
End If
Next Cell
CountCol = I
End Function
Then in the cell where you would like the total, put the following formula:
=CountCol(a1:a100,6)
That formula will show you the total count of all cells with colorindex of 6 (Yellow) in range a1:a100. You can fix it up to use color names if you like, but this works fine for me. There is a listing of basic colorindexes in Excel's help.
Hope this helps,
Leif
|
|
|
New Member
|
|
Mar 23, 2005, 10:32 PM
|
|
is there anyway to recalculate the total if a colour is changed? As it seems excel doesn't class a fill colour as a value.
|
|
|
New Member
|
|
Mar 24, 2005, 08:07 AM
|
|
Re-calcs
There are cleaner (and more complicated) ways to auto-calc, but the simplest I can offer is this. Add:
Application.Volatile (True)
Above the "Dim" statements in the code I listed before.
Then, just hit "F9" every time you want the cells to recalc.
If you want it to recalc automatically, you'll have to create a class module and override one of the application object's standard events. Here's a link for you to check out if interested:
HTML Code:
http://www.microsoft.com/exceldev/articles/xlevnts.htm
Leif
|
|
|
New Member
|
|
Apr 26, 2006, 07:10 AM
|
|
I have the same issue. I want to count the number of entries that are "RED". I know there is an excel formula that does it because I have done it before. Unfortunately I have deleted the work book that had the formula so I am searching for it again.
example: =countif(a1:a15, fontcolor="red") or something like that... I have tried all the variations I can think of so I must not have it right.
Any ideas?
|
|
|
New Member
|
|
Jun 2, 2010, 09:08 PM
|
|
First open the Workbook in which you wish to count or sum cells by a fill color. Now go into the Visual Basic Editor via Tools>Macro>Visual Basic Editor (Alt+F11) and then, from within the Visual Basic Editor go to Insert>Module to insert a standard module. Now, in this module, enter the code as shown below;
Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
Dim rCell As Range
Dim lCol As Long
Dim vResult
''''''''''''''''''''''''''''''''''''''
'Written by Ozgrid Business Applications
'www.ozgrid.com
'Sums or counts cells based on a specified fill color.
'''''''''''''''''''''''''''''''''''''''
lCol = rColor.Interior.ColorIndex
If SUM = True Then
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = WorksheetFunction.SUM(rCell,vResult)
End If
Next rCell
Else
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = 1 + vResult
End If
Next rCell
End If
ColorFunction = vResult
End Function
________________________________________
You can now use the custom function (ColorFunction) like;
=ColorFunction($C$1,$A$1:$A$12,TRUE) to SUM the values in range of cells $A$1:$A$12 that have the same fill color as cell $C$1. The reason it will SUM in this example is because we have used TRUE as the last argument for the custom function.
To COUNT these cells that have the same fill color as cell $C$1 you could use:
=ColorFunction($C$1,$A$1:$A$12,FALSE) or =ColorFunction($C$1,$A$1:$A$12) by omitting the last argument our function will automatically default to using FALSE.
Be aware that the changing of a cells fill color will not cause the Custom Function to recalculate, even if you press F9 (Recalculates the whole Workbook). You will need to either, select the cell and re-enter the formula, or go to Edit>Replace and replace = with =, or use Ctrl+Alt+F9
Try also to avoid the use of Application.Volatile as it will not help in this case and only slow down Excel's calculation time.
|
|
|
New Member
|
|
Jul 15, 2010, 04:24 AM
|
|
Thanks to retsoksirhc. His solution worked for me and I have been searching online since last week!
Thanks
|
|
|
New Member
|
|
Aug 29, 2012, 02:33 PM
|
|
This works great for counting the number in the cell, which is what it says it does. But I want to count the number of cells that are that color, regardless of what value is inside (the values will be letters). I've tried modifying the module but haven't been successful. Any help on how to get excel to do this?
Thank you!
James
First open the Workbook in which you wish to count or sum cells by a fill color. Now go into the Visual Basic Editor via Tools>Macro>Visual Basic Editor (Alt+F11) and then, from within the Visual Basic Editor go to Insert>Module to insert a standard module. Now, in this module, enter the code as shown below;
Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
Dim rCell As Range
Dim lCol As Long
Dim vResult
''''''''''''''''''''''''''''''''''''''
'Written by Ozgrid Business Applications
'www.ozgrid.com
'Sums or counts cells based on a specified fill color.
'''''''''''''''''''''''''''''''''''''''
lCol = rColor.Interior.ColorIndex
If SUM = True Then
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = WorksheetFunction.SUM(rCell,vResult)
End If
Next rCell
Else
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = 1 + vResult
End If
Next rCell
End If
ColorFunction = vResult
End Function
________________________________________
You can now use the custom function (ColorFunction) like;
=ColorFunction($C$1,$A$1:$A$12,TRUE) to SUM the values in range of cells $A$1:$A$12 that have the same fill color as cell $C$1. The reason it will SUM in this example is because we have used TRUE as the last argument for the custom function.
To COUNT these cells that have the same fill color as cell $C$1 you could use:
=ColorFunction($C$1,$A$1:$A$12,FALSE) or =ColorFunction($C$1,$A$1:$A$12) by omitting the last argument our function will automatically default to using FALSE.
Be aware that the changing of a cells fill color will not cause the Custom Function to recalculate, even if you press F9 (Recalculates the whole Workbook). You will need to either, select the cell and re-enter the formula, or go to Edit>Replace and replace = with =, or use Ctrl+Alt+F9
Try also to avoid the use of Application.Volatile as it will not help in this case and only slow down Excel's calculation time.
|
|
|
Software Expert
|
|
Aug 29, 2012, 09:36 PM
|
|
The ColorFunction has 3 parameters, if you leave out the 3rd parameter or set it to FALSE, then it will give you a count, just like you want.
|
|
|
New Member
|
|
Oct 2, 2012, 01:39 PM
|
|
Thanks cltan... Your solution worked for me. Cheers!
|
|
Question Tools |
Search this Question |
|
|
Add your answer here.
Check out some similar questions!
Does Sincerity Count?
[ 20 Answers ]
I have often heard some Christians say that sincerity doesn't count if it is based on innacurate knowledge and that such ignorance can lead to ultimate destruction at the hands of God. I disagree with this concept. Do you?
They cite the following scripture to support their conclusion.
Romans...
Count in the Report
[ 5 Answers ]
Dear...
I have aquestion about the report .
I give you example .
Table:
Size Date
FCL 1 X 40' 2/6/2006
FCL 2 X 40' 5/6/2006
FCL 3 X 40' 9/6/2006
How to remove a character using Excel function
[ 3 Answers ]
Could anyone know if there is any Excel function that I can use to remove a certain character from a text string, e.g.
1). Remove space from "acurve is what"
2). Remove "-" from "acurve-is-what"
3). Remove "'" from "acurve'is"
And so on.
Thanks a lot!
Count my web page
[ 3 Answers ]
Please, tell me on what site I should register my web page in order to obtain information about how many people visited my web, from what country they accessed my web page. The latter is necessary.
Thanks.
View more questions
Search
|