[Solved] Excel counting how many cells the background color is red and the value 4 at the

Hello!
I have an excel file like this:


I was able to use the function to program that automatically counts how many times each person listed in column K appeared in column C, automatically count me how many times a specific card with numbers 1-15 from column N appears in column B, now I need to automatically counted me how many times for a particular number from column N appearing in column B in the range D6: E111 there is a field with a red background color.
in the P column it is to count how many times "1" has a red background, if "2" is red it is to count in Q etc.

I am asking for help in programming the function, I do not know how to make it count based on the background color.

Attached I add a file to further illustrate the problem. Fields P6: S19 I filled in manually to show how it is to be calculated in the end.

Use a simple custom function for this


Basic color indexes: red - 3; green - 4 (but check the color tone because they will either have a different index or are not in the palette)

https://www.elektroda.pl/rtvforum/topic1681130.html

I introduced in cell S8 (I added 2 columns and 2 lines in relation to the sample file) the following formula = COUNT CONDITIONS ($ E $ 8: $ E $ 113; $ S $ 7; $ B $ 8: $ B $ 113; $ Q8) which checks times the card number is listed in column B and how many times there is 1 in the given range, I am not sure exactly how and when using which function exactly to add the formula checking the color NumerColor (E8: E113; 3), so that it calculates only for a specific numbers of red boxes.

When I add it as another condition:
= COUNTIFS ($ E $ 8: $ E $ 113, $ S $ 7, $ 8 $ b: $ B $ 113, $ Q8; E8: E113; LiczKolor (E8: E113 3))
although colored only have one field in this range returns 5


will anyone with more knowledge help? unfortunately I have stalled ...

As I answered you at PW

If you read all the entries from your thread carefully (also from the linked one) then you would have a large mine of knowledge about "counting colors"

To begin with, it must be established that:
- methods for counting cell background colors only apply to manual formatting or macro formatting - they do not apply to conditional formatting at all ;
- what version of Office you are working in - from version 2007 it is possible to filter by color and indirectly to count background colors;
- what you want to get through your formula:
= IF (VLOOKUP ($ N $ 8; B8: B21: 1); LiczKolor (D8 21; 3), "")
parse: a test expression can have only two outputs: TRUE or False -
and your SEARCH - if it was created correctly it will give you only the result
value of the "found" cell in the search range i.e. = $ N $ 8 or error # N / A! if in this range this value will not be (as in your sample file)

Added after 5 [hours] 53 [minutes]:

mar-ilo wrote:

= COUNT CONDITIONS ($ E $ 8: $ E $ 113; $ S $ 7; $ B $ 8: $ B $ 113; $ Q8) which checks how many times the card number is listed in column B and how many times in the given range there is 1,

To be precise, this formula only counts those cases where both conditions are simultaneously met - "card number" = 1 and "answered question" No. 1 -

mar-ilo wrote:

= COUNTIFS ($ E $ 8: $ E $ 113, $ S $ 7, $ 8 $ b: $ B $ 113, $ Q8; E8: E113; LiczKolor (E8: E113 3))
although colored only have one field in this range returns 5

The COUNTIF CONDITIONS function (Range1; Condition1; Range2; Condition2; ...) has a strict structure and you cannot enter the wrong highlighting criterion without specifying the range3

To prepare some sensible formula - I would need to understand how it should work Who On what basis? Colors these cells red? Can it be marked differently?

Now that VBA, you can expand the "CountColor" function to "CountColorIf", e.g.

Then in P21 you enter: and drag to the right.

A similar function (adding variables) you can "count small" in the range P6: S20, and leave the summation as it is.

Prot wrote:

... the methods for counting cell background colors only apply to manual formatting or macro formatting - they do not apply to conditional formatting at all ...

Free my "observation":
One can only tell if " at all "a color is applied in conditional formatting, referring to, for example, no color at all, and even provide the number of that color, e.g.

"you can't" say (probably in 99% of cases?) or just at this moment it is displayed.
In case of very simple formulas probably it is feasible, because one might be tempted to analyze:

for example:
each of the objects:

regarding cell content ...

... but it really "would like to have to be wanted by someone", sit on it and write a doctoral dissertation, for the sake of satisfaction "moving gray cells", whatever that means or doesn't mean ...

mar-ilo wrote:

I am asking for help in programming the function, I do not know how to make it count based on the background color.

After a deeper analysis of your "problem", I come to the conclusion that the easiest solution in your case will be to change the sheet structure - using a different layout (e.g. as in the screenshot)
2018-04...png Download (43.69 kB)

will allow you to use the simplest formulas for simple summation or conditional sums to perform the calculations you need
protpy..lsx Download (14.18 kB)Points: 1.5 for user

adamas_nt wrote:

Now that VBA, you can expand the "CountColor" function to "CountColorIf", e.g.

It actually counts how many times what value is colored red but for all the rows together and I would like to add that it compares the card number from column N (value 1, 2, 3, 4 etc.) with the items in column B and counts for specific cards. In P6 it would count only colored lines for which in column B there will be 1

Currently it counts all occurrences of red 1, 2, 3 and 4 and I would like it to count in individual rows when in column N there will be the same value as in column B

Prot wrote:

After a deeper analysis of your "problem", I come to the conclusion that the easiest solution in your case will be to change the construction of the sheet - using a different layout (eg as in the screenshot)

Unfortunately, the file layout cannot be changed, it must remain as it is.

Many thanks adamas_nt - works great