Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Using function based on "Font Color"
Could you please inform me if there is a function which can count and/or
summarize cells in excel based on "Font Color" |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Using function based on "Font Color"
Function CountCells(rng As Range, ci As Long)
For each cell in rng If cell.Font.Colorindex = ci then CountCells = CountCells +1 End If Next cell End Function =CountCells(A1:B10,3) -- __________________________________ HTH Bob "danai_mp" wrote in message ... Could you please inform me if there is a function which can count and/or summarize cells in excel based on "Font Color" |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Using function based on "Font Color"
Thank you very much for your answer but unfortunately I am not so good in this
I need more help If I have A1 = 7 (red colour), A2 =5 (green colour), A3 = 4 (lime colour), A4 = 2 (green colour), A5 =6 (red colour), A6 =2 (red colour) etc what can I use to have (a) the sum of red colour cells and (b) the count of green colour cells? thank you -- Best regards danai_mp Ο χρήστης "Bob Phillips" *γγραψε: Function CountCells(rng As Range, ci As Long) For each cell in rng If cell.Font.Colorindex = ci then CountCells = CountCells +1 End If Next cell End Function =CountCells(A1:B10,3) -- __________________________________ HTH Bob "danai_mp" wrote in message ... Could you please inform me if there is a function which can count and/or summarize cells in excel based on "Font Color" |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Using function based on "Font Color"
Here is a slightly different way.
Add this function Function CellColours(rng As Range) As Variant Dim ary As Variant Dim cell As Range Dim i As Long ReDim ary(1 To rng.Rows.Count) i = 1 For Each cell In rng ary(i) = cell.Font.ColorIndex i = i + 1 Next cell CellColours = ary End Function and then sum like =SUMPRODUCT(--(CellColours(A1:A10)=3) ,A1:A10) and simply count like =SUMPRODUCT(--(CellColours(A1:A10)=10)) -- __________________________________ HTH Bob "danai_mp" wrote in message ... Thank you very much for your answer but unfortunately I am not so good in this I need more help If I have A1 = 7 (red colour), A2 =5 (green colour), A3 = 4 (lime colour), A4 = 2 (green colour), A5 =6 (red colour), A6 =2 (red colour) etc what can I use to have (a) the sum of red colour cells and (b) the count of green colour cells? thank you -- Best regards danai_mp ? ??????? "Bob Phillips" ???????: Function CountCells(rng As Range, ci As Long) For each cell in rng If cell.Font.Colorindex = ci then CountCells = CountCells +1 End If Next cell End Function =CountCells(A1:B10,3) -- __________________________________ HTH Bob "danai_mp" wrote in message ... Could you please inform me if there is a function which can count and/or summarize cells in excel based on "Font Color" |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formatting "IF" response based on "OR" function | Excel Worksheet Functions | |||
in excel, can I use values based on color for an "if, then" condi | Excel Worksheet Functions | |||
Different Font Color for results evaluated by "IF" | New Users to Excel | |||
Macro to concatenate into "B1" B2 thru B"x" based on new data in "Col A" | Excel Discussion (Misc queries) | |||
Any IF(A2=font color=red", "1", "0") in excel? Or a way to do it? | Excel Discussion (Misc queries) |