Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I use COUNTIF stmnt if I want to find cells that are red
Can COUNTIF work if I want to count cells that are highlighted, let's say, red?
Thank you. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I use COUNTIF stmnt if I want to find cells that are red
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. "kisboros" wrote in message ... Can COUNTIF work if I want to count cells that are highlighted, let's say, red? Thank you. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I use COUNTIF stmnt if I want to find cells that are red
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 "kisboros" wrote: Can COUNTIF work if I want to count cells that are highlighted, let's say, red? Thank you. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I use COUNTIF stmnt if I want to find cells that are red
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 -- Biff Microsoft Excel MVP "FSt1" wrote in message ... 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 "kisboros" wrote: Can COUNTIF work if I want to count cells that are highlighted, let's say, red? Thank you. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I use COUNTIF stmnt if I want to find cells that are red
There is a workaround using auto filter since you can filter by colour and
then use a SUBTOTAL formula -- Regards, Peo Sjoblom "T. Valko" wrote in message ... 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 -- Biff Microsoft Excel MVP "FSt1" wrote in message ... 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 "kisboros" wrote: Can COUNTIF work if I want to count cells that are highlighted, let's say, red? Thank you. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I use COUNTIF stmnt if I want to find cells that are red
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. -- Biff Microsoft Excel MVP "Peo Sjoblom" wrote in message ... There is a workaround using auto filter since you can filter by colour and then use a SUBTOTAL formula -- Regards, Peo Sjoblom "T. Valko" wrote in message ... 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 -- Biff Microsoft Excel MVP "FSt1" wrote in message ... 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 "kisboros" wrote: Can COUNTIF work if I want to count cells that are highlighted, let's say, red? Thank you. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I use COUNTIF stmnt if I want to find cells that are red
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 "kisboros" wrote in message ... Can COUNTIF work if I want to count cells that are highlighted, let's say, red? Thank you. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I use COUNTIF stmnt if I want to find cells that are re
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 wont 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 cells 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 "T. Valko" wrote: 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 -- Biff Microsoft Excel MVP "FSt1" wrote in message ... 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 "kisboros" wrote: Can COUNTIF work if I want to count cells that are highlighted, let's say, red? Thank you. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I use COUNTIF stmnt if I want to find cells that are red
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 "kisboros" wrote: Can COUNTIF work if I want to count cells that are highlighted, let's say, red? Thank you. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I use COUNTIF stmnt if I want to find cells that are re
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 "Shane Devenshire" wrote in message ... 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 "T. Valko" wrote: 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 -- Biff Microsoft Excel MVP "FSt1" wrote in message ... 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 "kisboros" wrote: Can COUNTIF work if I want to count cells that are highlighted, let's say, red? Thank you. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to find cells that refer to data in other cells in excel | Excel Discussion (Misc queries) | |||
IF stmnt with a nested AND statement - need help | Excel Discussion (Misc queries) | |||
from a group of cells.find average of cells containing values | Excel Discussion (Misc queries) | |||
Using COUNTIF to find how many times 2004 occurs | Excel Worksheet Functions | |||
Using COUNTIF to find numbers within a range greater than the mean | Excel Worksheet Functions |