I have a worksheet in which i have used conditional formatting to gray out
certain values in a column. There are approximately 10 columns across all
with conditional formatting. Is anyone aware of a way to "count" the number
of cells in each row that are not grayed out?

On Oct 20, 11:22*am, Lisa L <Lisa
wrote:
I have a worksheet in which i have used conditional formatting to gray out
certain values in a column. *There are approximately 10 columns across all
with conditional formatting. *Is anyone aware of a way to "count" the number
of cells in each row that are not grayed out?

Not by using the format as some sort of lookup criteria. A UDF could
do this, but built-in functions can't.

However, you should be able to reproduce the condition for the
conditional format and work from there with Countif or SumProduct.
What is your condition?

Use COUNTIF or SUMPRODUCT, and use the same condition you've used in your
conditional formatting.
--
David Biddulph

"Lisa L" <Lisa wrote in message
...
I have a worksheet in which i have used conditional formatting to gray out
certain values in a column. There are approximately 10 columns across all
with conditional formatting. Is anyone aware of a way to "count" the
number
of cells in each row that are not grayed out?

It would have helped if you posted your Conditional Format (CF) formula. You
can use the CF formula in worksheet formula (without the leading equal sign,
of course) in order to count which cells met that condition or, as in your
case, not. For example, if your CF formula was this...

=MOD(A1:J400,3)=0)

then the worksheet formula to count the cells not shaded as a result of that
formula would be this...

=SUMPRODUCT((NOT(MOD(A1:J400,3)=0))*(A1:J400<""))

or, taking advantage of the Boolean nature of this particular expression, we
can change the logical test from = to < and remove the NOT function call...

=SUMPRODUCT((MOD(A1:J400,3)<0)*(A1:J400<""))

You will have to apply these ideas (and adjust the ranges) for your actual
situation (or post your CF formulas and data columns reference for us to
see).

--
Rick (MVP - Excel)


"Lisa L" <Lisa wrote in message
...
I have a worksheet in which i have used conditional formatting to gray out
certain values in a column. There are approximately 10 columns across all
with conditional formatting. Is anyone aware of a way to "count" the
number
of cells in each row that are not grayed out?

I did not manually type a conditional function - I used the "conditional
formatting" button under the home/styles tab. I don't know how to write out
a conditional format.

Let me try to explain my worksheet a little better. It's a chart for a
football pool. Each row has a participants' picks for each game going across
the columns. So each column counts for one game that could have two possible
outcomes. I.e. - The Broncos are playing the Patriots so the column will have
one of the two teams whichever the participant picks to win. Once all the
games are played - I used to have to go through each column and select all
the losing teams and right click - format cell and change the formatting to
gray out the cells so they become losses. Then I discovered "conditional
formatting" which is pretty much error proof. So now I am able to select the
column and type in the losing team and it automatically formats the entire
column for me. Once all the losing teams have been filled gray and I have to
manually count the ungrayed cells in each row. These count as wins. This
was easy when we only had 20 people playing but now there are 95 and i'm
afraid i'm going to make an error.

I'm sure it would be so much easier if you were able to see the worksheet
but any help you can provide would be much appreciated.





"David Biddulph" wrote:

Use COUNTIF or SUMPRODUCT, and use the same condition you've used in your
conditional formatting.
--
David Biddulph

"Lisa L" <Lisa wrote in message
...
I have a worksheet in which i have used conditional formatting to gray out
certain values in a column. There are approximately 10 columns across all
with conditional formatting. Is anyone aware of a way to "count" the
number
of cells in each row that are not grayed out?

So you actually want to count the number of cells in a column that contains
the winning team's name then, right? Assuming the Patriots won and that
Column A has the team names in it (Rows 2 to at least 200), then try this
formula...

=COUNTIF(A2:A200,"Patriots")

--
Rick (MVP - Excel)


"Lisa L" wrote in message
...
I did not manually type a conditional function - I used the "conditional
formatting" button under the home/styles tab. I don't know how to write
out
a conditional format.

Let me try to explain my worksheet a little better. It's a chart for a
football pool. Each row has a participants' picks for each game going
across
the columns. So each column counts for one game that could have two
possible
outcomes. I.e. - The Broncos are playing the Patriots so the column will
have
one of the two teams whichever the participant picks to win. Once all the
games are played - I used to have to go through each column and select all
the losing teams and right click - format cell and change the formatting
to
gray out the cells so they become losses. Then I discovered "conditional
formatting" which is pretty much error proof. So now I am able to select
the
column and type in the losing team and it automatically formats the entire
column for me. Once all the losing teams have been filled gray and I have
to
manually count the ungrayed cells in each row. These count as wins. This
was easy when we only had 20 people playing but now there are 95 and i'm
afraid i'm going to make an error.

I'm sure it would be so much easier if you were able to see the worksheet
but any help you can provide would be much appreciated.





"David Biddulph" wrote:

Use COUNTIF or SUMPRODUCT, and use the same condition you've used in your
conditional formatting.
--
David Biddulph

"Lisa L" <Lisa wrote in message
...
I have a worksheet in which i have used conditional formatting to gray
out
certain values in a column. There are approximately 10 columns across
all
with conditional formatting. Is anyone aware of a way to "count" the
number
of cells in each row that are not grayed out?

No - I want to count the number of losses each participant has in each row.
I'm sorry it's very difficult to explain. Is there anyway I can show you the
spreadsheet?

"Rick Rothstein" wrote:

So you actually want to count the number of cells in a column that contains
the winning team's name then, right? Assuming the Patriots won and that
Column A has the team names in it (Rows 2 to at least 200), then try this
formula...

=COUNTIF(A2:A200,"Patriots")

--
Rick (MVP - Excel)


"Lisa L" wrote in message
...
I did not manually type a conditional function - I used the "conditional
formatting" button under the home/styles tab. I don't know how to write
out
a conditional format.

Let me try to explain my worksheet a little better. It's a chart for a
football pool. Each row has a participants' picks for each game going
across
the columns. So each column counts for one game that could have two
possible
outcomes. I.e. - The Broncos are playing the Patriots so the column will
have
one of the two teams whichever the participant picks to win. Once all the
games are played - I used to have to go through each column and select all
the losing teams and right click - format cell and change the formatting
to
gray out the cells so they become losses. Then I discovered "conditional
formatting" which is pretty much error proof. So now I am able to select
the
column and type in the losing team and it automatically formats the entire
column for me. Once all the losing teams have been filled gray and I have
to
manually count the ungrayed cells in each row. These count as wins. This
was easy when we only had 20 people playing but now there are 95 and i'm
afraid i'm going to make an error.

I'm sure it would be so much easier if you were able to see the worksheet
but any help you can provide would be much appreciated.





"David Biddulph" wrote:

Use COUNTIF or SUMPRODUCT, and use the same condition you've used in your
conditional formatting.
--
David Biddulph

"Lisa L" <Lisa wrote in message
...
I have a worksheet in which i have used conditional formatting to gray
out
certain values in a column. There are approximately 10 columns across
all
with conditional formatting. Is anyone aware of a way to "count" the
number
of cells in each row that are not grayed out?

You can send it to me (remove the NO.SPAM stuff from my email address), but
given me a good description of what you need from which columns.

--
Rick (MVP - Excel)


"Lisa L" wrote in message
...
No - I want to count the number of losses each participant has in each
row.
I'm sorry it's very difficult to explain. Is there anyway I can show you
the
spreadsheet?

"Rick Rothstein" wrote:

So you actually want to count the number of cells in a column that
contains
the winning team's name then, right? Assuming the Patriots won and that
Column A has the team names in it (Rows 2 to at least 200), then try this
formula...

=COUNTIF(A2:A200,"Patriots")

--
Rick (MVP - Excel)


"Lisa L" wrote in message
...
I did not manually type a conditional function - I used the "conditional
formatting" button under the home/styles tab. I don't know how to
write
out
a conditional format.

Let me try to explain my worksheet a little better. It's a chart for a
football pool. Each row has a participants' picks for each game going
across
the columns. So each column counts for one game that could have two
possible
outcomes. I.e. - The Broncos are playing the Patriots so the column
will
have
one of the two teams whichever the participant picks to win. Once all
the
games are played - I used to have to go through each column and select
all
the losing teams and right click - format cell and change the
formatting
to
gray out the cells so they become losses. Then I discovered
"conditional
formatting" which is pretty much error proof. So now I am able to
select
the
column and type in the losing team and it automatically formats the
entire
column for me. Once all the losing teams have been filled gray and I
have
to
manually count the ungrayed cells in each row. These count as wins.
This
was easy when we only had 20 people playing but now there are 95 and
i'm
afraid i'm going to make an error.

I'm sure it would be so much easier if you were able to see the
worksheet
but any help you can provide would be much appreciated.





"David Biddulph" wrote:

Use COUNTIF or SUMPRODUCT, and use the same condition you've used in
your
conditional formatting.
--
David Biddulph

"Lisa L" <Lisa wrote in message
...
I have a worksheet in which i have used conditional formatting to
gray
out
certain values in a column. There are approximately 10 columns
across
all
with conditional formatting. Is anyone aware of a way to "count"
the
number
of cells in each row that are not grayed out?

Upload the file to http://www.savefile.com/

You will get a URL from that.

Post the URL back here so anyone can download the file.


Gord Dibben MS Excel MVP

On Mon, 20 Oct 2008 12:07:16 -0700, Lisa L
wrote:

No - I want to count the number of losses each participant has in each row.
I'm sorry it's very difficult to explain. Is there anyway I can show you the
spreadsheet?

"Rick Rothstein" wrote:

So you actually want to count the number of cells in a column that contains
the winning team's name then, right? Assuming the Patriots won and that
Column A has the team names in it (Rows 2 to at least 200), then try this
formula...

=COUNTIF(A2:A200,"Patriots")

--
Rick (MVP - Excel)


"Lisa L" wrote in message
...
I did not manually type a conditional function - I used the "conditional
formatting" button under the home/styles tab. I don't know how to write
out
a conditional format.

Let me try to explain my worksheet a little better. It's a chart for a
football pool. Each row has a participants' picks for each game going
across
the columns. So each column counts for one game that could have two
possible
outcomes. I.e. - The Broncos are playing the Patriots so the column will
have
one of the two teams whichever the participant picks to win. Once all the
games are played - I used to have to go through each column and select all
the losing teams and right click - format cell and change the formatting
to
gray out the cells so they become losses. Then I discovered "conditional
formatting" which is pretty much error proof. So now I am able to select
the
column and type in the losing team and it automatically formats the entire
column for me. Once all the losing teams have been filled gray and I have
to
manually count the ungrayed cells in each row. These count as wins. This
was easy when we only had 20 people playing but now there are 95 and i'm
afraid i'm going to make an error.

I'm sure it would be so much easier if you were able to see the worksheet
but any help you can provide would be much appreciated.





"David Biddulph" wrote:

Use COUNTIF or SUMPRODUCT, and use the same condition you've used in your
conditional formatting.
--
David Biddulph

"Lisa L" <Lisa wrote in message
...
I have a worksheet in which i have used conditional formatting to gray
out
certain values in a column. There are approximately 10 columns across
all
with conditional formatting. Is anyone aware of a way to "count" the
number
of cells in each row that are not grayed out?