Count the Colored Cells in each column (seperate Count for Green and Red)

H

Huizar

Board Regular
Joined
Aug 11, 2016
Messages
94
Hello All,

I'm looking for a formula to count the amount of Green Cells and Red cells in a given column. Under my grand total I want the count for Green cells for the column and under the Green cell count I want the red cell count.

Essentially, it would look like this:

Grand Total:20
Green (Pass):6
Red(Fail):2

Thanks
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Sort by date Sort by votes
How are the cells given the color? If by Conditional Formatting then you can use the same formula used to set the color. If done manually you would need to use VBA.
 
Upvote 0
I applied the conditional formatting to only specific cells that have the word Main across (by filtering for the word). If I apply the conditional formatting would it capture everything down the column?

Thanks in advance
 
Upvote 0
A
B
1
header
2
90
3
60
4
80
5
70
6
66
7
99
8
95
9
97
10
77
11
12
13
Grand total
14
green
4

<tbody>
</tbody>

Say your conditional formatting condition for green if the number was 90 or above formant green

Then to get the count of green you would use this to get the count of greater then or equal to 90 cells in the range A2:A10
Code: 
=SUMPRODUCT(--(A2:A10>=90))

You would use a similar formula for red but use the condition that make the cell red
 
Last edited:
Upvote 0
Yes, I see that. but lets say that row 11 had a 98 and my conditional formatting did not apply to that cell. How would I exclude row 11 from the count?
 
Upvote 0
Huizar said:
Yes, I see that. but lets say that row 11 had a 98 and my conditional formatting did not apply to that cell. How would I exclude row 11 from the count?
Click to expand...
By including a test for whatever you used to filter by into your formula as well.
 
Last edited:
Upvote 0
Hi MrExcel,

I just tried that and it just returned a result of #VALUE!. What am I doing wrong here?

The formula I used is below:
=SUMPRODUCT(--($D$8:$BB$9,$D$17:$BB$18,$D$32:$BB$33,$D$71:$BB$72,$D$95:$BB$96,$D$109:$BB$110,$D$128:$BB$129,$D$147:$BB$148,$D$178:$BB$179,$D$187:$BB$188,$D$204:$BB$205,$D$218:$BB$219,$D$226:$BB$227,$D$276:$BB$277,$D$309:$BB$310,$D$341:$BB$342,$D$353:$BB$354,$D$369:$BB$370,$D$390:$BB$391,$D$407:$BB$408>=0.26))


Also Tried this and same result as above:

=SUMPRODUCT((--(D8:D9,D17:D18,D32:D33,D71:D72,D95:D96,D109:D110,D128:D129,D147:D148,D178:D179,D187:D188,D204:D205,D218:D219,D226:D227,D276:D277,D309:D310,D341:D342,D353:D354,D369:D370,D390:D391,D407:D408>=26.1%)))
Thanks
 
Last edited:
Upvote 0
Huizar said:
Hi MrExcel,

I just tried that and it just returned a result of #VALUE!. What am I doing wrong here?

The formula I used is below:
=SUMPRODUCT(--($D$8:$BB$9,$D$17:$BB$18,$D$32:$BB$33,$D$71:$BB$72,$D$95:$BB$96,$D$109:$BB$110,$D$128:$BB$129,$D$147:$BB$148,$D$178:$BB$179,$D$187:$BB$188,$D$204:$BB$205,$D$218:$BB$219,$D$226:$BB$227,$D$276:$BB$277,$D$309:$BB$310,$D$341:$BB$342,$D$353:$BB$354,$D$369:$BB$370,$D$390:$BB$391,$D$407:$BB$408>=0.26))


Also Tried this and same result as above:

=SUMPRODUCT((--(D8:D9,D17:D18,D32:D33,D71:D72,D95:D96,D109:D110,D128:D129,D147:D148,D178:D179,D187:D188,D204:D205,D218:D219,D226:D227,D276:D277,D309:D310,D341:D342,D353:D354,D369:D370,D390:D391,D407:D408>=26.1%)))
Thanks
Click to expand...
It looks like you tried to type out the ranges from your filter (although what is being filtered on is not all that clear)... that is not what I was suggesting. You mentioned something about the filter applying to "only specific cells that have the word Main across". Since you didn't say, let's assume you are looking in Column D for the word "Main" in order to do your filter and let's assume the cells in Column B are being colored green when the cell is greater than 0.26 and Column D's cell equals "Main" and assume your data runs from Row 8 to Row 408, then I this formula would give you the count for the red cells (ColorIndex=3)...

=COUNTIFS(C8:C408,">0.26",D8:D408,"Main")
 
Upvote 0
Mr. Excel,

Yes, I was working with the Countifs earlier. Just couldn't wrap my head around all this data. But yes. the countifs with "Main" is my best bet and it worked. For counting the reds and just change the sign to "<0.27" and it seem to work. When you were talking about the colorindex=3, what are you referring to? How would that apply to the formula?

Thanks for the help.
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question
Counting by cell colours
Replies
1
Views
350
Scott Huish
Scott Huish
J
  • Question
Cell Count by Color
Replies
5
Views
149
Fluff
Fluff
A
  • Question
VBA to change cell colour based on content
Replies
3
Views
145
Snakehips
Snakehips
S
  • Question
Count precentage of cells filled with a specific color
Replies
2
Views
416
ranman256
ranman256
N
  • Question
Stacked Chart to show value and status YTD
Replies
2
Views
277
neilo1969
N

Forum statistics

Threads
1,214,583
Messages
6,120,380
Members
448,955
Latest member
BatCoder

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top