A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Auto Sum + Conditional Formatting



 
 
Thread Tools Display Modes
  #1  
Old June 15th, 2004, 03:25 PM
BenJAMMIN
external usenet poster
 
Posts: n/a
Default Auto Sum + Conditional Formatting

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
  #2  
Old June 15th, 2004, 03:34 PM
Andy B
external usenet poster
 
Posts: n/a
Default Auto Sum + Conditional Formatting

Hi

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

--
Andy.


"BenJAMMIN" wrote in message
...
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



  #3  
Old June 15th, 2004, 04:24 PM
BenJAMMIN
external usenet poster
 
Posts: n/a
Default Auto Sum + Conditional Formatting

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" wrote:

Hi

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

--
Andy.


"BenJAMMIN" wrote in message
...
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




  #4  
Old June 15th, 2004, 04:35 PM
Andy B
external usenet poster
 
Posts: n/a
Default Auto Sum + Conditional Formatting

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.


"BenJAMMIN" wrote in message
...
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" wrote:

Hi

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

--
Andy.


"BenJAMMIN" wrote in message
...
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






  #5  
Old June 15th, 2004, 05:19 PM
BenJAMMIN
external usenet poster
 
Posts: n/a
Default Auto Sum + Conditional Formatting

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" wrote:

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.


"BenJAMMIN" wrote in message
...
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" wrote:

Hi

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

--
Andy.


"BenJAMMIN" wrote in message
...
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






  #6  
Old June 15th, 2004, 05:24 PM
Andy B
external usenet poster
 
Posts: n/a
Default Auto Sum + Conditional Formatting

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

--
Andy.


"BenJAMMIN" wrote in message
...
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" wrote:

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.


"BenJAMMIN" wrote in message
...
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" wrote:

Hi

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

--
Andy.


"BenJAMMIN" wrote in message
...
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








  #7  
Old June 15th, 2004, 05:29 PM
Andy B
external usenet poster
 
Posts: n/a
Default Auto Sum + Conditional Formatting

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

--
Andy.


"Andy B" wrote in message
...
OK. Try this:
=COUNTIF(B8:G24,""&"")&"/"&102

--
Andy.


"BenJAMMIN" wrote in message
...
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" wrote:

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.


"BenJAMMIN" wrote in message
...
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" wrote:

Hi

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

--
Andy.


"BenJAMMIN" wrote in message
...
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










 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:14 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.