Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMCOLOR with conditional formatting not working
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMCOLOR with conditional formatting not working
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") -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "duketter" wrote: 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMCOLOR with conditional formatting not working
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! "Luke M" wrote: 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") -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "duketter" wrote: 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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMCOLOR with conditional formatting not working
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. "duketter" wrote: 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! "Luke M" wrote: 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") -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "duketter" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional formatting in VBA not working | Excel Discussion (Misc queries) | |||
Conditional Formatting ROW not working | Excel Discussion (Misc queries) | |||
Conditional Formatting isnt working right | Excel Discussion (Misc queries) | |||
Conditional Formatting is not working... | Excel Worksheet Functions | |||
conditional formatting not working in every cell | Excel Discussion (Misc queries) |