Formula to autosum cells that have the same conditional formatting color.

[hotrod]

Cells have formula with conditional formatting.
I’m trying to autosum all the cells that have the same color background in a row.

EG add all green cells.

Range is A1 to DB1, then I want to copy and paste the formula down the rest of the rows.

 

[Datsmart]

If the cells are colored by Conditonal Formatting, then your code won't be able to count that way.
The range.interior.colorindex is NOT changed by a conditional format.

The best bet is to count the number of times the condition exists, not the color...
For example, if your conditional format for column A is =A1>10 (simple example, but should get the point)
To get the count, you would use a formula like =COUNTIF(A:A,">10")

 

[hilyete]

If you want to use a formula to autosum the values in a row that meet a certain criteria, you'll need to base it on the same criteria that makes the cell turn green to begin with. Click on one of the cells and then check the conditonal formatting to see what the formula is that's being used to trigger the color change. You would use that same criteria in your formula, as you won't be able to refer to the cell's color in a formula. For example, if the formula in the Conditional Formatting that causes the cell to turn green is "=A1>100 " then you would construct your formula based on that: SUMIF(A1:DB1,">100")

 

[hotrod]

Hiliete

I have tried a number of different SUMIF formulas but cannot get it to work.

Conditional formatting formula is for cell A1 is …
=A1>=LARGE($A1:$DB1,10)

 

[hilyete]

=SUMIF(A1:DB1,LARGE($A1:$DB1,10))

This sums all the values in cells A1 thru DB1 that are equal to the tenth largest value in the cells. So if the 10th largest number is 20, then any cell that has 20 in it, will be summed. If 5 cells have "20" in them, then the total will be "100". Is that what you were hoping to do?

 

[hotrod]

=SUMIF(A1:DB1,LARGE($A1:$DB1,10)) – total is $864.25

Green cells numbers are ...
1459.39
941.56
1816.48
1610.43
1944.44
894.92
1345.64
920.33
1101.38
864.25
TOTAL $12898.82

What I want is a formula that will add all the numbers in the green cells.
Total should be 12898.82 and not 864.25

 

[Jonmo1]

hilyete was close, he got all the ones that equalled the 10th largest (exactly).
But according to your CF formula,
=A1>=LARGE($A1:$DB1,10)

You want all those that are equal to OR greater than the 10th largest...

Try

=SUMIF(A1:DB1,">="&LARGE($A1:$DB1,10))

 

[hotrod]

Problem solved.
Thank you all very much for your help.

 

[hilyete]

Thanks for pointing that out jonmo1... I keep telling myself that I've got to wear my glasses while I'm at the computer!