Calculating Cells That Qualifiy A Conditional Formatting Rule

[danikva]

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]21%[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]20%[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]14%[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]31%[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]7%[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]12%[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I'd like to create a formula in cell (E1) that will sum all cells that qualify a conditional formatting rule.

For Example: If the conditional formatting rule says A1>=$D$1 (than fill green). This would apply to all cells in column A. In this example cell E1 will show 52% (21%+31%).(unfortunately this page won't let me fill in cells)

What I'm trying to have in cell E1 is the sum of all qualifying cell (green cell) from column A.

As I chose to change the percentage in cell D1, E1 may also change as cells in column A may loss or gain qualifiers. For example if I change D1 from 20% to 10% than cell E1 will change from 52% to 78% (21%+14%+31%+12%).

How can I formulate E1 to sum all color filled cells in column A?

I know I can accomplish what I want in cell E1 by going column B1 and input =if(A1>=$D$1,A1,"") and drag the formula down the column and then going to E1 and input = sum(B1:B5). However I want to have E1 calculated by the qualifiers in the conditional format.

 

[etaf]

use sumif
=SUMIF(A2:A5,">="&D1)

Excel Workbook
	A	B	C	D	E
1
2	A	B	C	D	E
3	21%			20%	52%
4	14%
5	31%
6	7%
7	12%
Sheet1

 

[blaow]

Why are you insisting on using the conditional formatting?


I would forget the conditional formatting and use:

=SUMIF(B2:B6, ">=" &E2)

 

[danikva]

use sumif
=SUMIF(A2:A5,">="&D1)

Sheet1

*	A	B	C	D	E
*	*	*	*	*
*	*
*	*	*	*
*	*	*	*
*	*	*	*
*	*	*	*

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody>
[TD="bgcolor: #CACACA, align: center"]1[/TD]

[TD="bgcolor: #CACACA, align: center"]2[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]

[TD="bgcolor: #CACACA, align: center"]3[/TD]
[TD="align: center"]21%[/TD]

[TD="align: center"]20%[/TD]
[TD="align: center"]52%[/TD]

[TD="bgcolor: #CACACA, align: center"]4[/TD]
[TD="align: center"]14%[/TD]

[TD="bgcolor: #CACACA, align: center"]5[/TD]
[TD="align: center"]31%[/TD]

[TD="bgcolor: #CACACA, align: center"]6[/TD]
[TD="align: center"]7%[/TD]

[TD="bgcolor: #CACACA, align: center"]7[/TD]
[TD="align: center"]12%[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula E3 =SUMIF(A3:A7,">="&D3) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Thank you, the Sumif formula works beautifully

 

[etaf]

your welcome