Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF: criteria is a format
I have a column with cells that have text of different colors. I'm trying to
count up the number of cells that the text is formatted a particular color. For example, A1 and A4 are formatted red text, while A2, A3, and A5 are formatted blue text. I need it to tell me that there are 2 cells with red and 3 cells with blue. Thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF: criteria is a format
It wouldn't be too difficult to write a macro to loop through a range
of cells and count the number of red and blue cells. I am not sure if you would want to just count red and blue and put the numbers in specific cells, or keep track of all the colors that are present. If there is some explainable logic as to why the cells that are red are red and why the cells that are blue are blue, you may find it more beneficial to make the cell font turn red or blue through the use of conditional formatting and do your counting with the same logic in a cell on the spreadsheet as you have in the "formula" in each of your conditional format conditions. That would effectively count the red and blue cells, with out the need for a macro; and it could save some time in adjusting the font. Good luck Ken Norfolk, Va On May 14, 6:46*pm, alligatormaki wrote: I have a column with cells that have text of different colors. *I'm trying to count up the number of cells that the text is formatted a particular color. * For example, A1 and A4 are formatted red text, while A2, A3, and A5 are formatted blue text. *I need it to tell me that there are 2 cells with red and 3 cells with blue. *Thanks. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF: criteria is a format
Maki,
You can use this UDF next to your data: Function GetCellColor(r as range) as Integer GetCellColor = r.font.colorindex End Function Now, next to A1 you can enter the formula: =getcellcolor(A1) Red is 3, Blue is 5. You can then count red cells with a formula like: =COUNTIF(B:B,3) HTH Kostis Vezerides On 15 Μάϊος, 01:46, alligatormaki wrote: I have a column with cells that have text of different colors. Â*I'm trying to count up the number of cells that the text is formatted a particular color. Â* For example, A1 and A4 are formatted red text, while A2, A3, and A5 are formatted blue text. Â*I need it to tell me that there are 2 cells with red and 3 cells with blue. Â*Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COUNTIF with various criteria | Excel Discussion (Misc queries) | |||
countif but have two criteria | New Users to Excel | |||
Countif using format criteria not number criteria? | Excel Worksheet Functions | |||
Countif using format criteria....not number criteria? | Excel Worksheet Functions | |||
countif criteria | Excel Worksheet Functions |