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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|