Auto Sum + Conditional Formatting

[Guest]

I would like to take one cell and have it with the Auto Sum feature. Here's the catch: I have applied a simple Conditional Formatting to a number of cells. The formatting is setup for blank formatting: equal to =""
There is nothing between the quotes, which tells the formatting that if there isnt anything in the cell, then give the cell a color. If ANY value is placed in the cell, take away the color.

So with that said, is there a way to have the Auto Sum feature add up those cells and tell me how many that are left that need a value? For example, if there are 60 cells to begin with, and 32 of them have had a value placed in them, the Auto Sum cell would display the number 28.

Any ideas on how to get this to work or something similiar?
Thanks

 

[Andy B]

Hi

Couldn't you just count the empty cells?
=COUNTIF(A1:A60,"<>"&"")

--
Andy.

I would like to take one cell and have it with the Auto Sum feature.

Here's the catch: I have applied a simple Conditional Formatting to a number
of cells. The formatting is setup for blank formatting: equal to =""

There is nothing between the quotes, which tells the formatting that if

there isnt anything in the cell, then give the cell a color. If ANY value is
placed in the cell, take away the color.

So with that said, is there a way to have the Auto Sum feature add up

those cells and tell me how many that are left that need a value? For
example, if there are 60 cells to begin with, and 32 of them have had a
value placed in them, the Auto Sum cell would display the number 28.

 

[Guest]

Thanks Andy. This does work to keep a running total, adding each time a cell is filled in. Is there a formula that will display the total number of cells in that range, how many are filled and how many aren't filled? For example, 3 out of 27 cells.

 

[Andy B]

You could use something like:
=COUNTIF(A1:A60,"="&"")&"/"&60-COUNTIF(A1:A60,"="&"")
You know the whole range is 60 cells in total - because your list is A1:A60

--
Andy.

Thanks Andy. This does work to keep a running total, adding each time a

cell is filled in. Is there a formula that will display the total number of
cells in that range, how many are filled and how many aren't filled? For
example, 3 out of 27 cells.

 

[Andy B]

OK. Try this:
=COUNTIF(B8:G24,"<>"&"")&"/"&102

--
Andy.

So here is the actual formula that I used:

=COUNTIF(B8:G24,"="&"")&"/"&102-COUNTIF(B8:G24,"="&"")

Six columns with 17 rows = 102 cells

HOWEVER, when I plug in the COUNTIF formula above it displays 64/32. The

64 is half of the total 102 cells.

What I would like to see displayed is XX/102
So it seems that I must the formula backwards (the above example of 64/32,

the 32 should be first) and the formula is dividing the total number of
cells in half.

 

[Andy B]

Or, for the other way round:
=COUNTIF(B8:G24,"="&"")&"/&102"