Counting Colours
Hi, I have a group of cells, the colour of the cell is dependant on it's
value being either higher or lower than 'x'. The problem I have is that 'x' isn't constant throughout the cells. Example: A1 = higher or lower than 10 A2 = higher or lower than 7 A3 = higher or lower than 25 etc. These aren't the true values, just examples. Higher than 'x' the cell is green, lower and it's red. What formula, if any, do I put into another cell that can count the green or red cells in a given range? |
Counting Colours
Hi,
Alt+F11 to open Vb editor, right click 'this workbook' and insert module and paste the code below in. Call with =sumbycolor(A1:A20,B1) Where A1:a20 is the range to count and B1 is the colour you want to count Function SumByColor(Rng As Range, ClrRange As Range) As Double Dim c As Range, TempSum As Double, ColorIndex As Integer Application.Volatile ColorIndex = ClrRange.Interior.ColorIndex TempSum = 0 On Error Resume Next For Each c In Rng If c.Interior.ColorIndex = ColorIndex Then TempSum = TempSum + 1 End If Next c On Error GoTo 0 Set c = Nothing SumByColor = TempSum End Function Mike "Darren" wrote: Hi, I have a group of cells, the colour of the cell is dependant on it's value being either higher or lower than 'x'. The problem I have is that 'x' isn't constant throughout the cells. Example: A1 = higher or lower than 10 A2 = higher or lower than 7 A3 = higher or lower than 25 etc. These aren't the true values, just examples. Higher than 'x' the cell is green, lower and it's red. What formula, if any, do I put into another cell that can count the green or red cells in a given range? |
Counting Colours
BTW this won't work with conditional formatted colours. That can be done but
it's more difficult. Mike "Darren" wrote: Hi, I have a group of cells, the colour of the cell is dependant on it's value being either higher or lower than 'x'. The problem I have is that 'x' isn't constant throughout the cells. Example: A1 = higher or lower than 10 A2 = higher or lower than 7 A3 = higher or lower than 25 etc. These aren't the true values, just examples. Higher than 'x' the cell is green, lower and it's red. What formula, if any, do I put into another cell that can count the green or red cells in a given range? |
Counting Colours
Hi Darren
Take it the other way round, ie: How many cells have a value higher or lower than 10 How many cells have a value higher or lower than 7 ....and so on See help on COUNTIF or SUMPRODUCT functions. HTH Cordially Pascal "Darren" a écrit dans le message de news: ... Hi, I have a group of cells, the colour of the cell is dependant on it's value being either higher or lower than 'x'. The problem I have is that 'x' isn't constant throughout the cells. Example: A1 = higher or lower than 10 A2 = higher or lower than 7 A3 = higher or lower than 25 etc. These aren't the true values, just examples. Higher than 'x' the cell is green, lower and it's red. What formula, if any, do I put into another cell that can count the green or red cells in a given range? |
All times are GMT +1. The time now is 06:01 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com