Formula to count coloured cells

Hello All
I have a column of data in Col K,I would like to use a formula which will count the data as follows
A.number of cells in red font on blue background
B.number of cells in red font total
C.number of cells in green font on blue background
D.number of cells in green font total
E.number of cells in black font on white background
F.number of cells in black font total
The results for each in sequence to be in Cols b&c rows 1 to 3
eg.Red font cells on blue background = 208
Red font cells total = 673
The formula will then count the numeric data in Cols P&Q relating to each colour combination in Col K and place the results in Cols N&O rows 1 to 3.
eg.Total for red font on blue background in col P =2800
Total for red font on white background in col Q =19514
I have attached a worksheet which i hope will explain,many thanks for help given.

colour is not data.

If you manually colour cells, you don't add any information to the sheet that Excel can use. In later versions, you can filter by colour, but that does not do much good. You still cannot count or sum by colour.

Instead of manually colouring cells and then trying to find a way to summarise that data, take the better approach:

Use another column to enter a value that applies to the data in the row. Then use conditional formatting to colour the fill and font based on that hard and fast piece of data.

Now it's easy to report on the data, since there is a value that can be used for summing or counting.

So, for example, in a new column enter a 1 for all values with red on blue, enter a 2 for all values with red on white, etc. Then format all values to be black on white. Use conditional formatting to colour the original values, if the new column = 1, colour red on blue, if the new column = 2 colour red on white, etc.

Now you can also use Sumif/Sumifs or Countif/Countifs to report on the data, based on the new column.

And finally, the data sample would have been sufficient with 10 rows per colour set. I gave up trying to edit your sheet after I hit row 1500. Make it easy for us to help you. A small data sample will do. We don't need 3000 rows to understand what you want to achieve if you give clear specifications.

Or you can make a trick and filter colored values:

Click drop down arrow by "test" -> Filter by Color

Then you'll get a number of filtered cells...

Hi,
May be a solution with using the macro function of excel 4
The macro function is only available in the defined name from excel 2007
The syntax is Read.Cell(no_type;reference)
To read Font Color first caracter no_type is 24 and to read Background Color no_type is 63
You can hide the G and H column
Hope this helps
Best regards

Rather than using tricks and macros, I strongly advocate good practice worksheet design.

You can design a bad worksheet layout and then engage a fleet of helpers to pull you out of your dilemma. Next time around, you'll be in the same situation. Or if you need to add another color combination to the sheet. Or if a rule changes. Or ..., or ..., or ...

Save yourself the trouble and use a solution you can maintain.

Add a column to the data. Enter a code for each color combination, for example

1 -- red font on blue background
2 -- red font total
3 -- green font on blue background
4 -- green font total
5 -- black font on white background
6 -- black font total

Then use conditional formatting to color the cells.

Summing and counting of the different colors is now easy. Use Sumif, Countif or a pivot table.

Again: color is not data. Trying to make it into data is more work than actually adding the data to the sheet.

Hi,
@ Marcol
I never learned VBA, however it may not be too late
I get strange values ​​from line 1461 to line 3193
When using your code and the 2 UDFs function, the result of the font color for black shows the value of -4105 instead of 0 with Excel 4 macro ?
The result for the UDFs CellColor is OK
How can I fix it ?
Best regards

Hi
@ Marcol
Now everything is clear for my side, I started to learn some VBA
Thanks you for sharing