Count for specific format?

[Lisa L]

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?

 

[Spiky]

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?

 

[David Biddulph]

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

 

[Rick Rothstein]

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).

 

[Lisa L]

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.

 

[Rick Rothstein]

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

 

[Lisa L]

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]

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.

 

[Gord Dibben]

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