Home |
Search |
Today's Posts |
#1
|
|||
|
|||
interior.colorindex used with conditional formatting
Is there as yet a workaround for this? I have a table of values, where the
largest value in each row is colored thanks to conditional formatting. I would like to be able to sum all the colored cells for each column using the ..interior.colorindex property. wazooli |
#2
|
|||
|
|||
wazooli,
Let's say that your table is in A1:F10. In H1, enter the formula =MAX(A1:F1), and copy down to H2:H20. Then in cell A11, use the formula =SUMPRODUCT((A1:A10=$H$1:$H$10)*A1:A10) and copy to B11:F11. HTH, Bernie MS Excel MVP "Wazooli" wrote in message ... Is there as yet a workaround for this? I have a table of values, where the largest value in each row is colored thanks to conditional formatting. I would like to be able to sum all the colored cells for each column using the .interior.colorindex property. wazooli |
#3
|
|||
|
|||
not what i am looking for. perhaps a more detailed explanation would help.
i have 10 columns, each containing 300 rows. each column represents a different condition. there is only 1 maximum value in a row. what I want is to see which condition is best, simply by having the most maximum values totaled on the bottom. conditional formatting makes visual verification easy, but getting a concrete number is not so easy. "Bernie Deitrick" wrote: wazooli, Let's say that your table is in A1:F10. In H1, enter the formula =MAX(A1:F1), and copy down to H2:H20. Then in cell A11, use the formula =SUMPRODUCT((A1:A10=$H$1:$H$10)*A1:A10) and copy to B11:F11. HTH, Bernie MS Excel MVP "Wazooli" wrote in message ... Is there as yet a workaround for this? I have a table of values, where the largest value in each row is colored thanks to conditional formatting. I would like to be able to sum all the colored cells for each column using the .interior.colorindex property. wazooli |
#4
|
|||
|
|||
wazooli,
Did you actually try my solution? Since your table is, presumably, in A1:J300, simply use the formula =MAX(A1:J1) in cell L1, and copy down to L2:L300. Then in A301, use the formula =SUMPRODUCT((A1:A300=$L$1:$L$300)*A1:A300) and copy to B301:J301 HTH, Bernie MS Excel MVP "Wazooli" wrote in message ... not what i am looking for. perhaps a more detailed explanation would help. i have 10 columns, each containing 300 rows. each column represents a different condition. there is only 1 maximum value in a row. what I want is to see which condition is best, simply by having the most maximum values totaled on the bottom. conditional formatting makes visual verification easy, but getting a concrete number is not so easy. "Bernie Deitrick" wrote: wazooli, Let's say that your table is in A1:F10. In H1, enter the formula =MAX(A1:F1), and copy down to H2:H20. Then in cell A11, use the formula =SUMPRODUCT((A1:A10=$H$1:$H$10)*A1:A10) and copy to B11:F11. HTH, Bernie MS Excel MVP "Wazooli" wrote in message ... Is there as yet a workaround for this? I have a table of values, where the largest value in each row is colored thanks to conditional formatting. I would like to be able to sum all the colored cells for each column using the .interior.colorindex property. wazooli |
#5
|
|||
|
|||
I did, and it summed the max values, rather than merely counting how many
were in each column. I am going to try and work out some code which counts a specific conditional format. perhaps that is the better way. "Bernie Deitrick" wrote: wazooli, Did you actually try my solution? Since your table is, presumably, in A1:J300, simply use the formula =MAX(A1:J1) in cell L1, and copy down to L2:L300. Then in A301, use the formula =SUMPRODUCT((A1:A300=$L$1:$L$300)*A1:A300) and copy to B301:J301 HTH, Bernie MS Excel MVP "Wazooli" wrote in message ... not what i am looking for. perhaps a more detailed explanation would help. i have 10 columns, each containing 300 rows. each column represents a different condition. there is only 1 maximum value in a row. what I want is to see which condition is best, simply by having the most maximum values totaled on the bottom. conditional formatting makes visual verification easy, but getting a concrete number is not so easy. "Bernie Deitrick" wrote: wazooli, Let's say that your table is in A1:F10. In H1, enter the formula =MAX(A1:F1), and copy down to H2:H20. Then in cell A11, use the formula =SUMPRODUCT((A1:A10=$H$1:$H$10)*A1:A10) and copy to B11:F11. HTH, Bernie MS Excel MVP "Wazooli" wrote in message ... Is there as yet a workaround for this? I have a table of values, where the largest value in each row is colored thanks to conditional formatting. I would like to be able to sum all the colored cells for each column using the .interior.colorindex property. wazooli |
#6
|
|||
|
|||
Wazooli,
Well, in your original post you said. "I would like to be able to SUM all the colored cells for each column using the .interior.colorindex property." To COUNT them, simply use =SUMPRODUCT((A1:A300=$L$1:$L$300)*1) HTH, Bernie MS Excel MVP "Wazooli" wrote in message ... I did, and it summed the max values, rather than merely counting how many were in each column. I am going to try and work out some code which counts a specific conditional format. perhaps that is the better way. "Bernie Deitrick" wrote: wazooli, Did you actually try my solution? Since your table is, presumably, in A1:J300, simply use the formula =MAX(A1:J1) in cell L1, and copy down to L2:L300. Then in A301, use the formula =SUMPRODUCT((A1:A300=$L$1:$L$300)*A1:A300) and copy to B301:J301 HTH, Bernie MS Excel MVP "Wazooli" wrote in message ... not what i am looking for. perhaps a more detailed explanation would help. i have 10 columns, each containing 300 rows. each column represents a different condition. there is only 1 maximum value in a row. what I want is to see which condition is best, simply by having the most maximum values totaled on the bottom. conditional formatting makes visual verification easy, but getting a concrete number is not so easy. "Bernie Deitrick" wrote: wazooli, Let's say that your table is in A1:F10. In H1, enter the formula =MAX(A1:F1), and copy down to H2:H20. Then in cell A11, use the formula =SUMPRODUCT((A1:A10=$H$1:$H$10)*A1:A10) and copy to B11:F11. HTH, Bernie MS Excel MVP "Wazooli" wrote in message ... Is there as yet a workaround for this? I have a table of values, where the largest value in each row is colored thanks to conditional formatting. I would like to be able to sum all the colored cells for each column using the .interior.colorindex property. wazooli |
#7
|
|||
|
|||
Hi Wazooli
Unfortunately, the answer Bernie gave you is correct, regardless of the colour of the cells. However, you could look at using a colour index Macro that will allow you to Count and Sum all of your coloured cells. You can find this at www.xldynamic.com/source/xld.ColourCounter.html Be warned though, this won't work with conditional formatted cells. So you will have to remove the conditional formats. HTH Michael "Wazooli" wrote: Is there as yet a workaround for this? I have a table of values, where the largest value in each row is colored thanks to conditional formatting. I would like to be able to sum all the colored cells for each column using the .interior.colorindex property. wazooli |
#8
|
|||
|
|||
Wazooli
Chip Pearson has code for determining the CF colors based on the condition(s) input. http://www.cpearson.com/excel/CFColors.htm Gord Dibben Excel MVP On Thu, 24 Feb 2005 14:07:03 -0800, "Michael" wrote: Hi Wazooli Unfortunately, the answer Bernie gave you is correct, regardless of the colour of the cells. However, you could look at using a colour index Macro that will allow you to Count and Sum all of your coloured cells. You can find this at www.xldynamic.com/source/xld.ColourCounter.html Be warned though, this won't work with conditional formatted cells. So you will have to remove the conditional formats. HTH Michael "Wazooli" wrote: Is there as yet a workaround for this? I have a table of values, where the largest value in each row is colored thanks to conditional formatting. I would like to be able to sum all the colored cells for each column using the .interior.colorindex property. wazooli |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
should be possible to add more conditions to conditional formatti. | Excel Discussion (Misc queries) | |||
Effect of Conditional Formatting, Data Validation | Excel Discussion (Misc queries) | |||
conditional formatting conflict? | Excel Worksheet Functions | |||
Conditional formatting not available in Excel | Excel Discussion (Misc queries) | |||
copy conditional format as ACTUAL format | Excel Discussion (Misc queries) |