SUMCOLOR with conditional formatting not working

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?
 
L

Luke M

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")
 
D

duketter

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!
 
D

duketter

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top