I have a conditional cell color based on a formula to identify blank cells &
populated cells. I want to now count the cells that meet the formula
criteria. I get accurate counts with the populated cell formula count but
because I have an isblank in my blank cell conditional formula I am getting a
miscount. Is there any really good way to count colors based on conditional
formulas especially where the condition is blank?

You should have no trouble if use use COUNTBLANK. You should get a count of
blank cells no matter how they are formatted
--
Gary''s Student
gsnu200705


"bnkone" wrote:

> I have a conditional cell color based on a formula to identify blank cells &
> populated cells. I want to now count the cells that meet the formula
> criteria. I get accurate counts with the populated cell formula count but
> because I have an isblank in my blank cell conditional formula I am getting a
> miscount. Is there any really good way to count colors based on conditional
> formulas especially where the condition is blank?

Sorry, I probably should have written more details.

Here is the formula I use to color cells conditionally where I am having
trouble

=AND(INDEX(data,MATCH($A1,trademarks,1),MATCH(A$8,countries,1))=1,ISBLANK(A1))

This condition generates a yellow cell fill. At the end of the day I want to
count how many yellows I have.

I use this for red:
=AND(INDEX(data,MATCH($A1,trademarks,1),MATCH(A$8,countries,1))<>1,A1>0)

I use this formula to count the color formulas
=IF(AND(INDEX(data,MATCH('Color Sheet'!$A9,trademarks,1),MATCH('Color
Sheet'!B$8,countries,1))=1,'Color
Sheet'!B9>0)=TRUE,"G",IF(AND(INDEX(data,MATCH('Color
Sheet'!$A9,trademarks,1),MATCH('Color Sheet'!B$8,countries,1))=1,'Color
Sheet'!B8="")=TRUE,"Y",IF(AND(INDEX(data,MATCH('Color
Sheet'!$A9,trademarks,1),MATCH('Color Sheet'!B$8,countries,1))<>1,'Color
Sheet'!B9>0)=TRUE,"R","")))


I can then count all the cells with the formula for red and get an accurate
count. However, the isblank is throwing off my cell counts for yellow.


"Gary''s Student" wrote:

> You should have no trouble if use use COUNTBLANK. You should get a count of
> blank cells no matter how they are formatted
> --
> Gary''s Student
> gsnu200705
>
>
> "bnkone" wrote:
>
> > I have a conditional cell color based on a formula to identify blank cells &
> > populated cells. I want to now count the cells that meet the formula
> > criteria. I get accurate counts with the populated cell formula count but
> > because I have an isblank in my blank cell conditional formula I am getting a
> > miscount. Is there any really good way to count colors based on conditional
> > formulas especially where the condition is blank?

First see:

http://www.xldynamic.com/source/xld.CFConditions.html

The methods described here use great piles of VBA. An alternative approach
is to use a helper column. Enter the conditional formulae directly into the
cells in the helper column. You will see TRUEs and FALSEs in the helper
column that match to the colors in the column having conditional formats.

Then all you need to do is use COUNTIF() on the helper column. I am sorry I
don't have a more compact solution.
--
Gary's Student
gsnu200705


"bnkone" wrote:

> Sorry, I probably should have written more details.
>
> Here is the formula I use to color cells conditionally where I am having
> trouble
>
> =AND(INDEX(data,MATCH($A1,trademarks,1),MATCH(A$8,countries,1))=1,ISBLANK(A1))
>
> This condition generates a yellow cell fill. At the end of the day I want to
> count how many yellows I have.
>
> I use this for red:
> =AND(INDEX(data,MATCH($A1,trademarks,1),MATCH(A$8,countries,1))<>1,A1>0)
>
> I use this formula to count the color formulas
> =IF(AND(INDEX(data,MATCH('Color Sheet'!$A9,trademarks,1),MATCH('Color
> Sheet'!B$8,countries,1))=1,'Color
> Sheet'!B9>0)=TRUE,"G",IF(AND(INDEX(data,MATCH('Color
> Sheet'!$A9,trademarks,1),MATCH('Color Sheet'!B$8,countries,1))=1,'Color
> Sheet'!B8="")=TRUE,"Y",IF(AND(INDEX(data,MATCH('Color
> Sheet'!$A9,trademarks,1),MATCH('Color Sheet'!B$8,countries,1))<>1,'Color
> Sheet'!B9>0)=TRUE,"R","")))
>
>
> I can then count all the cells with the formula for red and get an accurate
> count. However, the isblank is throwing off my cell counts for yellow.
>
>
> "Gary''s Student" wrote:
>
> > You should have no trouble if use use COUNTBLANK. You should get a count of
> > blank cells no matter how they are formatted
> > --
> > Gary''s Student
> > gsnu200705
> >
> >
> > "bnkone" wrote:
> >
> > > I have a conditional cell color based on a formula to identify blank cells &
> > > populated cells. I want to now count the cells that meet the formula
> > > criteria. I get accurate counts with the populated cell formula count but
> > > because I have an isblank in my blank cell conditional formula I am getting a
> > > miscount. Is there any really good way to count colors based on conditional
> > > formulas especially where the condition is blank?