SUMCOLOR with conditional formatting not working

  • Thread starter Thread starter duketter
  • Start date Start date
D

duketter

Excel 2007 - I am trying to sum the numbers in a row (D2:V2) and if the cells
are highlighted in green or orange I want the numbers added. I am using the
sumcolor module. It works if I manually color/highlight the cell. However I
have the cell highlighted based on conditional formatting (for example: green
= numbers over 50,000) then the sumcolor function will not work. It won't
pick up the cells that are highlighted by conditional formatting. Here is my
formula:
=sumcolor($A$2,B7:V7)+sumcolor($A$3,B7:V7)

A2 (green) and A3 (orange) are the blank colored cells for reference. B7:V7
is the data I want to analyze and sum.

Can this be done with conditional formatting on?
 
Rather than summing by color, sum by the condition that creates the color.
Since green means > 50000, your formula is:
=SUMIF(B7:V7,">50000")

To sum a group of cells between 50000 and 10000, formula is:
=SUMIF(B7:V7,">10000)-SUMIF(B7:V7,">=50000")
 
Thanks for the response. However can we take this one step further. Green
means >50000. However if there isn't a dollar amount in a row (row 4 for
example) greater than 50000 than I highlighted in orange the highest dollar
amount cell for that row. Any idea how I can incorporate that into the
sumif? I understand how to do the green colors but how about the orange
since they are kind of random numbers (highest dollar amount cell in that
row).

Thanks!
 
One other note, the row will either have an orange highlighted cell or green
highlighted cell. Never both since the row either has a cell with value
greater than 50000 (highlighted in green) or a cell highlighted in orange
with the biggest dollar amount for that row which would be less than 50000.
 
Back
Top