How to Count or Sum cells with Colors

Manny74 · Apr 23, 2019

Hi All,

I have rows 1 thru 21, with data in column B.

How would I count or sum the number of green cells?

I have tried to google this and want to keep this simple, so I'm hoping I don't need to use VBA....

Sorry, I had colors on my spreadsheet, but when I copied it onto the body of my post, the colors (light green) disappeared, so I made the font Arial black (ex. B2, B4, etc.), so you can pretend those are the light green cells I need to count/sum.....

[TABLE="width: 128"]
<tbody>[TR]
[TD="width: 64"] 1[/TD]
[TD="class: xl75, width: 64"]B[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="class: xl68"]62.09%[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="class: xl69"]699[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="class: xl68"]75.40%[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="class: xl70"] 57[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="class: xl63"]0[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="class: xl70"] 0[/TD]
[/TR]
[TR]
[TD="align: right"] 8[/TD]
[TD="class: xl63"] 58[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="class: xl64"] 88.73%[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="class: xl65, width: 64"] 0%[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="class: xl71, width: 64"]1[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD="class: xl66"]71%[/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD="class: xl67, width: 64"] 33[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD="class: xl64"] 55.49%[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD="class: xl64"] 90.00%[/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[TD="class: xl72"]0.00[/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[TD="class: xl73"]0.00[/TD]
[/TR]
[TR]
[TD="align: right"]18[/TD]
[TD="class: xl72"]1.64[/TD]
[/TR]
[TR]
[TD="align: right"]19[/TD]
[TD="class: xl74"]511[/TD]
[/TR]
[TR]
[TD="align: right"]20[/TD]
[TD="class: xl72"]5.01[/TD]
[/TR]
[TR]
[TD="align: right"]21[/TD]
[TD="class: xl70"]708[/TD]
[/TR]
</tbody>[/TABLE]

Marcelo Branco · Apr 23, 2019

What is the criterion to color the cells?

M.

Scott T · Apr 23, 2019

If the coloring is done by conditional formatting then you can use the same condition to sum the cells. If they are manual colored the you would need VBA.

Fluff · Apr 23, 2019

How are the cells being coloured?

Manny74 · Apr 23, 2019

Well I used conditional formatting for each cell...
The user must meet a certain target for each cell (goal)

So for example, B2 (62.09), if it's equal to or greater than 50% then it would be green....

Manny74 · Apr 23, 2019

Yes I used conditional formatting for all the cells.
So how would I used conditional formatting to count the numbers of green cells?
Keep in mind, each cell has a different target amount....

Fluff · Apr 23, 2019

You could use
=COUNTIF(B:B,">50.0%")

Although your description & data don't match

Manny74 · Apr 23, 2019

I used conditional formatting.
Every cell represents the users score...I use conditional formatting to see which user met the target goal-score (green) and who did not...

Joe4 · Apr 23, 2019

Numbers like 699 and 57 would be greater than 50%, so would also be highlighted.
So, how did those ones NOT get highlighted?
What is your exact Conditional Formatting formula?
Or is the CF rule only being applied on certain rows (and not all of them)?

Marcelo Branco · Apr 23, 2019

Manny74 said:
Yes I used conditional formatting for all the cells.
So how would I used conditional formatting to count the numbers of green cells?
Keep in mind, each cell has a different target amount....

Maybe something like this

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A

[/TD]
[TD="bgcolor: #DCE6F1"]

B

[/TD]
[TD="bgcolor: #DCE6F1"]

C

[/TD]
[TD="bgcolor: #DCE6F1"]

D

[/TD]
[TD="bgcolor: #DCE6F1"]

E

[/TD]
[TD="bgcolor: #DCE6F1"]

F

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1

[/TD]
[TD][/TD]
[TD]

Values

[/TD]
[TD]

Target

[/TD]
[TD][/TD]
[TD]

Count

[/TD]
[TD]

Sum

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2

[/TD]
[TD][/TD]
[TD="bgcolor: #D8E4BC"]

62,09%

[/TD]
[TD]

50%

[/TD]
[TD][/TD]
[TD]

7

[/TD]
[TD]

4,4271

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3

[/TD]
[TD][/TD]
[TD]

699

[/TD]
[TD]

1000