Forum Discussion
Formula to count a range of text values and count by cell color.
Sergei - Your response did make sense but I have now changed my example for the the sum by color so the text by color is not sequential so you can go by A-D, e-F and G-I. I am wondering if there is another way to count by color. I am not familiar with how this can be done with Conditional Formatting. Thanks!
Hi Tom,
In addition - I checked your conditional formatting. You apply one rule foreach condition, thus have quite many of them. In general better to have one rule for each color and combine conditions in the rule.
In your case you need only 3 rules for each color, for red one
=ISNUMBER(SEARCH($R5,$N$5))
which you apply as
to entire your range, similar for other colors. Result will be
Same is in attached file for the right range. Formula to sum is based on the same conditions you use for the rules:
=SUMPRODUCT(ISNUMBER(SEARCH($R$5:$R$20,$N5))*1)
Sergie - Thank you very much for your replies. I thought there would be a simpler solution but evidently not. I will study these formulas and learn from them. Thanks again!