how do I use COUNTIF stmnt if I want to find cells that are red

[kisboros]

Can COUNTIF work if I want to count cells that are highlighted, let's say, red?
Thank you.

 

[Fred Smith]

You can't directly look for the color of a cell in a Countif.

You could, however, use in the Countif the same condition that turns the
cell red.

Regards,
Fred.

 

[FSt1]

hi
not the standard countif function. but you can install a custom UDF to count
by color. sum too. see this site.
http://www.cpearson.com/excel/colors.aspx
if you are using 2007, i have been told that these color functions are built
it. but i don't know that for sure.

Regards
FSt1

 

[T. Valko]

i have been told that these color functions are built it.

but i don't know that for sure.

No, there are no new color formula functions available in Excel 2007.

You can now filter and sort based on color in Excel 2007.

There were 5 new formula functions added to Excel 2007:

AVERAGEIF
AVERAGEIFS
COUNTIFS
SUMIFS
IFERROR

 

[Peo Sjoblom]

There is a workaround using auto filter since you can filter by colour and
then use a SUBTOTAL
formula

--


Regards,


Peo Sjoblom

 

[T. Valko]

You can filter on the color but there's still no (built -in) way to count
cells based on the color. The subtotal would be based on something like
COUNT or COUNTA where the cells just happen to be colored.

 

[Ashish Mathur]

Hi,

You can try the following but even before you use the procedure, please note
that this is a not dynamic I.e. if you colour more cells, you will not see
the result change:

1. Press Ctrl+F;
2. Click on Options and then click on the format button;
3. Click on Font and select Red in color;
4. Click on OK;
5. Now click on Find All;
6. Press Ctrl+A;
7. Close the Find box

You will now see all red font cells highlighted and the SUM, COUNT will
appear in the lower right corner. Please note that this procedure will not
work if cells are conditionally formatted.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Shane Devenshire]

Hi Biff,

Actually there are more new functions in 2007, not that I want any of them:

CUBESET - This function will fetch the set that is defined by the
set_expression parameter. Optional parameters allow you to specify the
ordering of the set as well as the caption to be displayed in the Excel cell
that contains this formula. (Note that the set itself won’t have a display
value.) For example, the formula: =CUBESET ("Adventure
Works","[Customer].[Customer Geography].[All
Customers].children","Countries") returns the set of countries in the
Customer Geography hierarchy and shows “Countries†as the cell’s display
value.

CUBEVALUE - This function will fetch the aggregated value from the cube
filtered by the various member_expression arguments. For example, the
formula: =CUBEVALUE ("Adventure Works","[Measures].[Gross
Profit]","[Product].[Category].[Bikes]","[Date].[Fiscal Year].[FY 2004]")
returns the value $5,035,271.22 which is the aggregated amount in the
Adventure Works cube for Gross Profit for Bikes in Fiscal 2004.

CUBEKPIMEMBER - This function returns a KPI (Key Performance Indicator) from
the OLAP cube.

CUBEMEMBER - This function will fetch the member or tuple defined by the
member_expression. For example, (from the illustration above,) the formula:
=CUBEMEMBER ("Adventure Works", "[Sales Reason].[On Promotion]") returns the
member named “On Promotion†from the “Sales Reason†dimension of the
Adventure Works cube.

CUBEMEMBERPROPERTY - This function returns a property of a member in the
OLAP cube.

CUBERANKEDMEMBER - This function returns the Nth item from a set. This can
be very useful when building a Top N (or Bottom N) report in Excel.

CUBESETCOUNT - This function returns the number of items in a set.
Typically the argument to this function will be a CUBESET function or a
reference to a CUBESET function.

Cheers,
Shane Devenshire

 

[Shane Devenshire]

Hi,

Here is a general custom function that counts the number of cells that have
a certain color.

Function CountFormats(R As Range, E As Range) As Integer
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

If this helps, please click the Yes button

Cheers,
Shane Devenshire

 

[T. Valko]

Actually there are more new functions in 2007, not that I want any of them:

Yeah, I forgot about those. Those are highly specialized functions for
working with OLAP cubes (something I never do).


--
Biff
Microsoft Excel MVP

Hi Biff,

Actually there are more new functions in 2007, not that I want any of
them:

CUBESET - This function will fetch the set that is defined by the
set_expression parameter. Optional parameters allow you to specify the
ordering of the set as well as the caption to be displayed in the Excel
cell
that contains this formula. (Note that the set itself won't have a
display
value.) For example, the formula: =CUBESET ("Adventure
Works","[Customer].[Customer Geography].[All
Customers].children","Countries") returns the set of countries in the
Customer Geography hierarchy and shows "Countries" as the cell's display
value.

CUBEVALUE - This function will fetch the aggregated value from the cube
filtered by the various member_expression arguments. For example, the
formula: =CUBEVALUE ("Adventure Works","[Measures].[Gross
Profit]","[Product].[Category].[Bikes]","[Date].[Fiscal Year].[FY 2004]")
returns the value $5,035,271.22 which is the aggregated amount in the
Adventure Works cube for Gross Profit for Bikes in Fiscal 2004.

CUBEKPIMEMBER - This function returns a KPI (Key Performance Indicator)
from
the OLAP cube.

CUBEMEMBER - This function will fetch the member or tuple defined by the
member_expression. For example, (from the illustration above,) the
formula:
=CUBEMEMBER ("Adventure Works", "[Sales Reason].[On Promotion]") returns
the
member named "On Promotion" from the "Sales Reason" dimension of the
Adventure Works cube.

CUBEMEMBERPROPERTY - This function returns a property of a member in the
OLAP cube.

CUBERANKEDMEMBER - This function returns the Nth item from a set. This
can
be very useful when building a Top N (or Bottom N) report in Excel.

CUBESETCOUNT - This function returns the number of items in a set.
Typically the argument to this function will be a CUBESET function or a
reference to a CUBESET function.

Cheers,
Shane Devenshire

No, there are no new color formula functions available in Excel 2007.

You can now filter and sort based on color in Excel 2007.

There were 5 new formula functions added to Excel 2007:

AVERAGEIF
AVERAGEIFS
COUNTIFS
SUMIFS
IFERROR