Sep 20 2017
01:43 PM
- last edited on
Jul 25 2018
10:05 AM
by
TechCommunityAP
Sep 20 2017
01:43 PM
- last edited on
Jul 25 2018
10:05 AM
by
TechCommunityAP
I have attached a file with data to explain the formula(s) I am seeking. In brief, how to count a range of grades in one formula and whether a range of grades can be counted by cell color.
Sep 20 2017 01:57 PM
Hi Tom,
That could be
=SUMPRODUCT(($F$5:$F$20>=LEFT($I22,1))*(($F$5:$F$20<=RIGHT($I22,1))))
if your range is in I22 as
a-d
Attached
Sep 20 2017 02:19 PM
Thanks Sergei!
Also, the example showing Column R with text in colored cells. Is there a formula to count the cells by color?
Sep 20 2017 02:38 PM
Tom, if you mean to check only color of the cell, not value - when not, at least by formulas (could be done using VBA). The color is the property of the cell, and formulas work with values.
But since you have conditional formatting you may use exactly the same expressions as in rules to calculated number of your coloured cells.
Perhaps i don't understand something, but i see no difference between counting as in previous time and summing. You may use same SUMPRODUCT as well. Please clarify if i'm wrong.
Sep 20 2017 02:56 PM
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!
Sep 20 2017 03:15 PM
Tom, the point is you have to define your ranges somehow. It that's list of characters like in your file
A,C,E,H
when we may calculate number of cells with letters which are founded in above atring:
=SUMPRODUCT(ISNUMBER(SEARCH($R$5:$R$20,$P25))*1)
Here we have only one condition, thus second multiplier is 1. In general it could be as many AND conditions as needed (previous time we used two).
Please see attached
Sep 21 2017 02:37 AM
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)
Sep 21 2017 09:36 AM
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!