conditional formula for blank cells

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

bnkone - 09 Feb 2007 19:56 GMT

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?

Reply to this Message

Gary''s Student - 09 Feb 2007 20:19 GMT

You should have no trouble if use use COUNTBLANK. You should get a count of
blank cells no matter how they are formatted

Signature

Gary''s Student
gsnu200705

> 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?

Reply to this Message

bnkone - 09 Feb 2007 20:35 GMT

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.

> You should have no trouble if use use COUNTBLANK. You should get a count of
> blank cells no matter how they are formatted
[quoted text clipped - 5 lines]
> > miscount. Is there any really good way to count colors based on conditional
> > formulas especially where the condition is blank?

Reply to this Message

Gary''s Student - 09 Feb 2007 22:13 GMT

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.

Signature

Gary's Student
gsnu200705

> Sorry, I probably should have written more details.
>
[quoted text clipped - 30 lines]
> > > miscount. Is there any really good way to count colors based on conditional
> > > formulas especially where the condition is blank?

Reply to this Message