Count colored cells generated by conditional formating

tdcsi · Sep 22, 2011

Using excel 2010, I have used conditional formatting to highlight cells of one column if the value is greater then a second column. I would like to be able to generate a value of the total highlighted cells so I can calculate the percentage of those values. Here is an excerpt of what I am using in which I have been manually changing the "Higher In-House Value" number each time I come across one. Is there a function or code that I can use?Sheet1<table class="html-maker-worksheet" border="1" cellspacing="0" cellpadding="0"><thead><tr><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr ><th>1</th><td style="text-align: center;;">Lot</td><td style="text-align: center;;">Received</td><td style="text-align: center;;">In-House</td><td style="text-align: center;;">Supplier</td><td></td><td></td><td></td><td></td></tr><tr ><th>2</th><td style="text-align: center;;">0225481</td><td style="text-align: center;;">4/28/2010</td><td style="text-align: center;;">80.0%</td><td style="text-align: center;;">80.2%</td><td></td><td style="text-align: center;;">Total # of Lots</td><td></td><td style="text-align: center;;">11</td></tr><tr ><th>3</th><td style="text-align: center;;">9224621</td><td style="text-align: center;;">7/20/2010</td><td style="text-align: center;;">81.0%</td><td style="text-align: center;;">80.7%</td><td></td><td style="text-align: center;;">Higher In-House value</td><td></td><td style="text-align: center;;">5</td></tr><tr ><th>4</th><td style="text-align: center;;">0225727</td><td style="text-align: center;;">7/20/2010</td><td style="text-align: center;;">82.0%</td><td style="text-align: center;;">81.2%</td><td></td><td style="text-align: center;;">% values higher In-House</td><td></td><td style="text-align: center;;">44.4%</td></tr><tr ><th>5</th><td style="text-align: center;;">0225505</td><td style="text-align: center;;">7/20/2010</td><td style="text-align: center;;">80.1%</td><td style="text-align: center;;">80.7%</td><td></td><td></td><td></td><td></td></tr><tr ><th>6</th><td style="text-align: center;;">0225440</td><td style="text-align: center;;">9/20/2010</td><td style="text-align: center;;">80.6%</td><td style="text-align: center;;">80.4%</td><td></td><td></td><td></td><td></td></tr><tr ><th>7</th><td style="text-align: center;;">0226035</td><td style="text-align: center;;">9/20/2010</td><td style="text-align: center;;">80.2%</td><td style="text-align: center;;">80.6%</td><td></td><td></td><td></td><td></td></tr><tr ><th>8</th><td style="text-align: center;;">0226013</td><td style="text-align: center;;">9/21/2010</td><td style="text-align: center;;">80.2%</td><td style="text-align: center;;">81.2%</td><td></td><td></td><td></td><td></td></tr><tr ><th>9</th><td style="text-align: center;;">0225505</td><td style="text-align: center;;">9/20/2010</td><td style="text-align: center;;">80.5%</td><td style="text-align: center;;">80.7%</td><td></td><td></td><td></td><td></td></tr><tr ><th>10</th><td style="text-align: center;;">0226013</td><td style="text-align: center;;">9/20/2010</td><td style="text-align: center;;">80.3%</td><td style="text-align: center;;">80.2%</td><td></td><td></td><td></td><td></td></tr><tr ><th>11</th><td style="text-align: center;;">0226013</td><td style="text-align: center;;">11/18/2010</td><td style="text-align: center;;">80.7%</td><td style="text-align: center;;">80.2%</td><td></td><td></td><td></td><td></td></tr><tr ><th>12</th><td style="text-align: center;;">0226201</td><td style="text-align: center;;">5/12/2011</td><td style="text-align: center;;">80.4%</td><td style="text-align: center;;">81.3%</td><td></td><td></td><td></td><td></td></tr><tr ><th>13</th><td style="text-align: center;;">1227068</td><td style="text-align: center;;">7/12/2011</td><td style="text-align: center;;">80.5%</td><td style="text-align: center;;">80.7%</td><td></td><td></td><td></td><td></td></tr><tr ><th>14</th><td style="text-align: center;;">1227399</td><td style="text-align: center;;">7/29/2011</td><td style="text-align: center;;">80.4%</td><td style="text-align: center;;">81.4%</td><td></td><td></td><td></td><td></td></tr><tr ><th>15</th><td style="text-align: center;;">1227399</td><td style="text-align: center;;">9/15/2011</td><td style="text-align: center;;">80.2%</td><td style="text-align: center;;">81.4%</td><td></td><td></td><td></td><td>Excel 2010<table ><tr><td style="padding:0.3em;border: 2px solid #000009;background-color:#FFFFFF;">

Excel Workbook

Cell

Formula

H2

=SUM(C2:C9999)

H4

=H3/H2

Worksheet Formulas

<br />

The formatting does change C3,C4,C6,C10 and C11 to yellow.

mikerickson · Sep 22, 2011

In H2, I'd use =COUNT(C2:C9999) rather than sum.
In H3, I'd ignore the conditional formatting color and test directly for the in-house being greater than supplier.
=SUMPRODUCT(--(C1:C9999>D1:D9999))

tdcsi · Sep 22, 2011

I guess I should proof my formula's before posting them. In my live version I do use count, however I have never used sumproduct and that works perfectly. Thank you.

Perhaps you can help me with my other problem. I had hoped the solution to this one would lead me to an easy fix with the second, and it probably is. Same situation, however the conditional formatting colors cells outside of a specific range. For example:

Excel Workbook

A

B

1

Low limit

6

2

High Limit

7

3

4

Lot#

Value

5

x

6.2

6

y

7.2

Sheet1

Excel 2010

Here cell B6 is highlighted because it falls outside the range of B1 and B2. I assume that I would have to nest an If statement into the sumproduct formula to get the same result as before. Any suggestions?

mikerickson · Sep 22, 2011

If you want to count the number of entrys in A1:A100 that are outside of the range defined by B1 and B2.

You want a count of the cells that are < B1 or > B2

The way to do this with SUMPRODUCT is
=SUMPRODCT(--((A1:A100 < B1)+(B2 < A1:A100)))

tdcsi · Sep 22, 2011

Thank you for you help. I've been trying to make things much harder than they had to be and this will save me a lot of time.

tdcsi · Sep 23, 2011

Just plugged in the formula into my live version and have a problem. How do I "omit" blank cells? It seems to want to assume that blank cells are below my lower limit, thus counting them and giving me an incorrect result. My spreadsheet is a living document and is continually being added to. The spreadsheet is set up with the first 23 rows containing product data and statistical formula's and rows 24-10000 containing data entry. Here is an abbreviated excerpt. I am currently using an if statement in an adjacent column then counting the out of range values and finally transferring that value to another place in the spreadsheet. What I would like to be able to do is omit the adjacent column (I), and have cell K22 count the out of range values set by H7 and H8. I intend on moving my final results (column J -M) and deleting rows 20-22, but I need to be able to generate the correct info first.

Excel Workbook

H

I

J

K

L

7

8.00

8

9.00

20

OOS

2

pH

OOS

21

Values

39

Average

8.44

2

22

% OOS

5.1%

Values

39

23

pH

pO

% OOS

5.1%

24

8.16

0

25

8.53

0

26

8.30

0

12070 Zutron Multi

Excel 2010

Cell Formulas
Range	Formula
L21	=$I$20
I20	=SUM(I24:I10000)
I21	=COUNT(H24:H10000)
I22	=I20/I21
I24	=IF(OR(H24<$H$7,H24>$H$8),1,0)
I25	=IF(OR(H25<$H$7,H25>$H$8),1,0)
I26	=IF(OR(H26<$H$7,H26>$H$8),1,0)
K20	=$H$23
K21	=$H$1
K22	=$I$21
K23	=$L$21/$K$22

mikerickson · Sep 23, 2011

=sumprodct(--((a1:a100 < b1)+(b2 < a1:a100))*(0 < a1:a100))

tdcsi · Sep 23, 2011

Thank you again.

Count colored cells generated by conditional formating

tdcsi

Board Regular

Excel Facts

mikerickson

MrExcel MVP

tdcsi

Board Regular

mikerickson

MrExcel MVP

tdcsi

Board Regular

tdcsi

Board Regular

mikerickson

MrExcel MVP

tdcsi

Board Regular

Similar threads

Forum statistics

Share this page

Count colored cells generated by conditional formating

Board Regular

Excel Facts

MrExcel MVP

Board Regular

MrExcel MVP

Board Regular

Board Regular

MrExcel MVP

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock