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

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

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

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

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

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

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

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