Calculating Cells That Qualifiy A Conditional Formatting Rule

danikva

New Member
Joined
Sep 3, 2013
Messages
13
ABCDE
121%20%
214%
331%
47%
512%

<tbody>
</tbody>
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.
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
use sumif
=SUMIF(A2:A5,">="&D1)

Excel Workbook
ABCDE
1
2ABCDE
321%20%52%
414%
531%
67%
712%
Sheet1
 
Upvote 0
Why are you insisting on using the conditional formatting?


I would forget the conditional formatting and use:

Code:
=SUMIF(B2:B6, ">=" &E2)
 
Upvote 0
use sumif
=SUMIF(A2:A5,">="&D1)

Sheet1

*ABCDE
1*****
2ABCDE
321%**20%52%
414%****
531%****
67%****
712%****

<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>
</tbody>

Spreadsheet Formulas
CellFormula
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
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,238
Members
448,555
Latest member
RobertJones1986

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