Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default In Excel, how do I "Countif" a cell is a certain format?

If I choose a series of cells and I want to determine how many of those cells
have a fill color of Red, how many have green, etc..., can I do that?
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: In Excel, how do I "Countif" a cell is a certain format?

Yes, you can use the COUNTIF function in Excel to count cells that meet a certain criteria, including cells with a specific fill color. Here's how:
  1. Select the range of cells you want to count.
  2. Click on the Home tab in the ribbon at the top of the Excel window.
  3. Click on the Conditional Formatting button in the Styles group.
  4. Choose Highlight Cells Rules and then More Rules from the dropdown menu.
  5. In the New Formatting Rule dialog box, select Use a formula to determine which cells to format.
  6. In the Format values where this formula is true field, enter the formula
    Formula:
    =CELL("color",A1)=
    (without the quotes), where "A1" is the first cell in your selected range. This formula will highlight any cells in the range that have a fill color of red.
  7. Click on the Format button and choose a fill color to use for the highlighted cells.
  8. Click OK to close the Format Cells dialog box, and then click OK again to close the New Formatting Rule dialog box.
  9. Now that you've highlighted the cells with a certain fill color, you can use the COUNTIF function to count them. For example, to count the number of cells with a red fill color, enter the formula
    Formula:
    =COUNTIF(A1:A10,CELL("color",A1)=1
    (without the quotes), where "A1:A10" is the range of cells you want to count.

Repeat the above steps for each fill color you want to count. Just make sure to change the formula in step 6 to
Formula:
=CELL("color",A1)=
for green,
Formula:
=CELL("color",A1)=
for blue, and so on.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,939
Default In Excel, how do I "Countif" a cell is a certain format?

If the colours are not conditional formats then check out this link...

http://www.xldynamic.com/source/xld.ColourCounter.html

If the colours are based on conditional formats then you can use regular
formulas...
--
HTH...

Jim Thomlinson


"Pheasants21" wrote:

If I choose a series of cells and I want to determine how many of those cells
have a fill color of Red, how many have green, etc..., can I do that?

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,344
Default In Excel, how do I "Countif" a cell is a certain format?

Hi,

You can add the following code to your workbook

Function CountFormats(R As Range, E As Range) As Integer
Application.Volatile
Dim cell As Range
Dim Total As Integer
Dim T As Boolean
Set S = E.Cells(1, 1)
Total = 0
For Each cell In R
T = True
With cell
If .Font.ColorIndex < S.Font.ColorIndex Then T = False
If .Interior.ColorIndex < S.Interior.ColorIndex Then T = False
If .Font.Bold < S.Font.Bold Then T = False
If .Font.Italic < S.Font.Italic Then T = False
If .Font.Underline < S.Font.Underline Then T = False
End With
If T = True Then
Total = Total + 1
End If
Next cell
CountFormats = Total
End Function

In the spreadsheet you can enter the function as follows:

=CountFormats(A1:F13,H9)

This function will count the number of cells in the range A1:F13 that have
the same formatting as cell H9. This function check five types of
formatting, you can remove any of those from the code. Font Color, Interior
Color, Bold, Italic, Underline. As written all these formats must match, so
if you only want to deal with fill color it would read

Function CountFormats(R As Range, E As Range) As Integer
Application.Volatile
Dim cell As Range
Dim Total As Integer
Dim T As Boolean
Set S = E.Cells(1, 1)
Total = 0
For Each cell In R
T = True
With cell
If .Interior.ColorIndex < S.Interior.ColorIndex Then T = False
End With
If T = True Then
Total = Total + 1
End If
Next cell
CountFormats = Total
End Function

--

Thanks,
Shane Devenshire


"Pheasants21" wrote:

If I choose a series of cells and I want to determine how many of those cells
have a fill color of Red, how many have green, etc..., can I do that?

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell Steve Kay Excel Discussion (Misc queries) 2 August 8th 08 01:54 AM
can I nest COUNTIF functions(C5:C8,"apples") AND (D5:D8,"green") guycummins Excel Worksheet Functions 4 June 10th 08 09:23 PM
EXCEL: How do I format a cell if value < 12 then show "LOW STOCK" CNSmith Excel Worksheet Functions 1 March 20th 07 02:46 AM
Format cell to display "Y" or "N"when entering a 1 or zero Brad Excel Discussion (Misc queries) 7 February 8th 07 07:50 PM
Help!!! Enter "7" in a cell and Excel changes the "7" to "11" immediately!!! [email protected] Excel Discussion (Misc queries) 3 January 5th 07 03:18 PM


All times are GMT +1. The time now is 11:40 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"