Using COUNTIF with Colors

Certain functions in Excel and My Excel Toolbox accept a single value but not an array or multi-cell range. Often it is necessary to create a helper column with the function in a column of cell formulas; first enter a formula at the head of the column, then copy the formula down the remainder of the column.
My most recent comment below discusses Excel 365's MAP function to create a virtual helper column as an array. The following function in My Excel Toolbox is similar to MAP:
    =ForEachItem(Items, Formula)
This function evaluates a Formula for each item in Items. The results are returned in an array matching the dimensions of Items, which can be a single value, an array of values (1D or 2D), or a range of cells. Formula must be the text equivalent of a cell formula using @ to represent one item in Items. Formula can begin with or without an equal sign, so "=SIN(@)" and "SIN(@)" are equivalent. Each item in Items will replace all instances of @ before Formula is evaluated for that item. You can use @ to escape @; i.e., @@ will be replaced by @ as text, not by an item in Items.
If Items is a range of cells, ForEachItem returns results from the following My Excel Toolbox function (with Items replacing Target):
    =ForEachCell(Target, Formula)
Target must be a range of cells. Only its first contiguous area applies; any other areas are ignored. For example, if Target is (A1:C3, D1, E1:F2), which has three areas, only A1:C3 applies. Formula is the same as before.
For example, if the value of A3 is 10, A4 is 20, B3 is abc, and B4 is xyz, then this formula
    =ForEachCell(A3:B4, "@ & ""@@"" & CELL(""address"", @)")
returns the following 2x2 array of text values:
    { 10@$A$3, abc@$B$3; 20@$A$4, xyz@$B$4 }
Notice when Formula is a quoted string, any quotation marks within the string must be doubled. This could be avoided if cell E3 contained the following text constant:
    @ & "@@" & CELL("address", @)
In this case, the previous formula could be replaced by
    =ForEachCell(A3:B4, E3)
The last formula in my most recent comment below was
    =SUM(--(MAP(A2:A25, LAMBDA(x, FillColor(x))) = FillColor(A8)))
Using ForEachItem instead of MAP, the following formula returns the same result:
    =SUM(--(ForEachItem(A2:A25, "FillColor(@)") = FillColor(A8)))
Notice ForEachItem could be replaced by ForEachCell because A2:A25 is a range of cells, not an array of values.
See https://sites.google.com/view/MyExcelToolbox/

Tomek's approach was to create a helper column in B with each cell containing the decimal RGB value of the fill color for the corresponding cell in column A, then use COUNTIF to count column B values that match the RGB value of a specified color. This is a typical solution method because his ClColor function can only return the fill color for a single cell. If it could return an array containing the fill color for each cell in a range of cells, then the helper column would not be necessary; in that case, this formula would count the number of cells with the same violet color as cell A8 (see Figure 1 in Tomek's first comment below):
    =SUM(--(ClColor(A2:A25) = ClColor(A8)))
Notice the following formula would fail because COUNTIF requires its first argument to be a range and will not accept an array even if ClColor could return one:
    =COUNTIF(ClColor(A2:A25), ClColor(A8))    --    returns #VALUE!
Excel 365 includes the MAP function, which offers another method to avoid the helper column. Here is the solution using MAP:
    =SUM(--(MAP(A2:A25, LAMBDA(x, ClColor(x))) = ClColor(A8)))
The MAP(A2:A25, LAMBDA(...)) function above replaces x by each cell in the range A2:A25, returning the desired array of ClColor results.
As described in my previous comment below, My Excel Toolbox's FillColor function can be substituted for Tomek's ClColor function in this discussion. So this formula returns the same result:
    =SUM(--(MAP(A2:A25, LAMBDA(x, FillColor(x))) = FillColor(A8)))
By the way, MAP will accept either a range or an array for its first argument.

My Excel Toolbox includes the following function to return the RGB fill color of a cell as a decimal value:
    =FillColor([Cell])
The default value of Cell is the formula's cell. If Cell is a multi-cell range, only its first cell applies. This function is essentially the same as the ClColor function described in Tomek's earliest comment below; it ignores conditional formatting.
My Excel Toolbox also includes the following function to convert a decimal RGB color value into a 6-character hexadecimal text value:
    =ColorAsHex(ColorRGB)
For example, the decimal RGB value for red is 255; its hexadecimal text value is FF0000.
To return the RGB fill color of a cell as hexadecimal text, use this formula:
    =ColorAsHex(FillColor([Cell]))
See https://sites.google.com/view/MyExcelToolbox/
For more about colors, see my earliest comment (2024-07-05) here: https://excelribbon.tips.net/T009092_Showing_RGB_Colors_in_a_Cell.html

Note to the picture in my previous comment:
The Figure 1 shows 5 violet cells found, but if you count the colors you will only find 4. That is because the cell A12 has conditional formatting applied to it with yellow background and it takes precedence when displayed. The explicit background color is still 15086320 as indicated in B12.

I just forgot remove conditional formatting, but it proves the point that this approach does not necessarily reflect what you see.

I actually suspect that Roger would like to count the cells by color that is defined by conditional formatting. Contrary to what Allen said, this information can be obtained by VBA by using the following syntax:
<cell>.DisplayFormat.Interior.Color
It will get the color of what is displayed, whether it is explicit, or overridden by conditional formatting.
Unluckily this syntax cannot be used in user defined functions, but can be used in subs.
I have some ideas how to overcome this limitation, so if someone is interested please reply here or send me an e-mail.

A different approach would be to create an user defined function that would return a color of the cell background, e.g,:
'===============================================
Public Function ClColor(cl As Range)
    ClColor = cl.Interior.Color
End Function
'==============================================
Then you can create a mirror range of cells containing the color values for the range of cells in the calendar, using a formula =ClColor(address) (see Figure 1 below) .
Let's assume this range is named "CalendarShades"
Then in a cell where you want the count enter:
=COUNTIFS(CalendarShades,15086320))
where the number 15086320 corresponds to violet color of the cell background in the example spreadsheet.
Note that the range of cells does not have to be contiguous or rectangular.
I suggest creating the mirror range on a second sheet, simply by creating the duplicate sheet, then replacing the content of the relevant cells with the formula for the color from original sheet.
Like it was stated in Allen's tip, this approach will also work only with cells that are explicitly filled with colors.