Formula for counting coloured cells.

[hotrod]

I have a table of numbers with conditional formating, formula's are...

=COUNTIF(OFFSET($W3:$AB3,1,0),W3+1)+COUNTIF(OFFSET($W3:$AB3,1,0),W3-1) - color, lemon.
=COUNTIF(OFFSET($W3:$AB3,-1,0),W3+1)+COUNTIF(OFFSET($W3:$AB3,-1,0),W3-1) - color, light blue.

Column AC, I want to count the number of cells that are lemon for each row.
Column AD, I want to count the number of cells that are light blue for each row.

Is it possible?

 

[Domski]

With conditional formatting the cells are not coloured in the same way that if you had done so manually. To count the cells that are a particular colour what you really need to do is count the ones that meet the criteria that you have specified in the conditional format.

Dom

 

[hotrod]

Dom
Sorry about not getting back earlier to respond.
What do I need to add to the conditional formula to count color?

 

[Kaps_mr2]

what criteria are used to make the cells go lemon and light blue etc ?

Kaps

 

[Hooseria]

Hi Hotrod.

As far as I'm aware, there is no formula to sum by colour or do any formula by colour, but I seem to be learning something new each day, anyway this is how I do it.with a user defined VBA Code.

Open Developer add this line of code, save, and Then back to your spread sheet. The formula SumCol(A4,A1:A10) will then be usable.

It will sum the entire range in col A 1 to 10 by the colour A4.

Hope it helps.

Function SumCol(ColourRange As Range, EntireRange As Range)

Dim ColourCell As Range
Dim CValue As Integer
Dim ColourTotal

CValue = ColourRange.Interior.ColorIndex

For Each ColourCell In EntireRange
If ColourCell.Interior.ColorIndex = CValue Then
ColourTotal = WorksheetFunction.Sum(ColourCell) + ColourTotal
End If
Next ColourCell

SumCol = ColourTotal
End Function

 

[Domski]

Kaps - I think the criteria that is being used is as posted above although I'm not sure exactly how it could be translated into a formula without looking into it more which I don't really have time to do today.

Hooseria - That would work if the cells were coloured using shading but I don't think conditional formatting is applied in the same way that interior.colorindex will be able to recognise.

Dom

 

[Hooseria]

Yikes, Domski

Today has been quite educational. I learnt, something else. Wow, I didn't know that, I had a look at a previous Post by Donkeyote, he mentioned the same thing as well.

You would have to use a formula to Sum similar to the formula to Condition format.

Its quite sad that VBA code does not identify Conditional Formatted properties.

Ok don't want to drift from the Thread.

I'm out, good luck guys.

 

[hotrod]

What a challenge! Thank you all for trying to help.
Sounds like I need to start again with what I want to do and try another way.

I have a table of numbers W2-AA2/W690-AA690.

The conditional formating highlights all the adjacent numbers, comparing top and bottom row.
=COUNTIF(OFFSET($W3:$AB3,1,0),W3+1)+COUNTIF(OFFSET($W3:$AB3,1,0),W3-1) - color, lemon.
=COUNTIF(OFFSET($W3:$AB3,-1,0),W3+1)+COUNTIF(OFFSET($W3:$AB3,-1,0),W3-1) - color, light blue.

Looks at rows 1 and 2 and changes cells to lemon in row 1 and blue in row 2 (adjacent numbers).
Looks at rows 2 and 3, changes cells to lemon in row 2 and blue for row 3.
Looks at row 3 and 4 etc.
For every row there are cells which are lemon and blue.

Then I want to know how many are lemon and blue for each row for columns AC (lemon) and AD (blue).

Any suggestions would be greatly appreciated.

 

[Hooseria]

Hotrod

It is impossible to draw up a formula to give a count of the colours, unless you post an HTML dataset and describe how you conditionally formatted your dataset, for the simple reason that the formula you gave, as a spreadsheet formula, counts the rows above (formula 2) if it has values either +1 or -1 the value of Cell W3(only).
As a conditional formatted value The first row is Blue(not Lemon) because it is (offset -1) (your BLue formula) of "Each" cell below it W3 to AB3, and that is only because I applied a conditional format of formula 2 to the Top Row. I need to know how you conditional formatted the dataset.

I Know what you want, but can't grasp the theory of the shading, Your statement that each line has either a yellow or a blue further threw a spanner in the works for me. My guess is we would have to do a Macro to loop through each column and give a count.

Please post a smaller version of your spread indicating your conditional formatting method.

 

[hotrod]

Hooseria, thanks for your time and perseverance.
Sorry, I was not able to work out how to add a small screen dump to forum.
I hope the following is clear enough for you to understand, try this example...

Table, A1-E1/A6-E6
4, 32, 33, 36, 40
25, 37, 40, 42, 43
3, 15, 30, 33, 39
1, 3, 10, 35, 41
3, 7, 12, 16, 34
14, 26, 28, 38 40

Conditional formatting (highlights adjacent numbers) is ...
=COUNTIF(OFFSET($A1:$E1,1,0),A1+1)+COUNTIF(OFFSET($A1:$E1,1,0),A1-1) - lemon
=COUNTIF(OFFSET($A1:$E1,-1,0),A1+1)+COUNTIF(OFFSET($A1:$E1,-1,0),A1-1) - blue

Table, cells colored...
Lemon
row 1 - 36
row 2 - 40
row 3 - none
row 4 - 35
row 5 - none
row 6 - none

Blue
row 1 - none
row 2 - 37
row 3 - 39
row 4 - none
row 5 - 34
row 6 - none

Then for F1-F6 (lemon) & G1-G6 (blue) I want a count of how many cells are lemon and blue for each row.
Prefer to have all formulas if possible so that I have instant results when I add information.