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
1
BenJAMMIN (21)
6/15/2004 2:25:01 PM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
1167 Views

Similar Articles

[PageSpeed] 33

Hi

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

-- 
Andy.


"BenJAMMIN" <BenJAMMIN@discussions.microsoft.com> wrote in message
news:E7148F6F-C8E5-485C-80AB-0FC26C0E6D97@microsoft.com...
> 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


0
andyb1 (494)
6/15/2004 2:34:46 PM
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" <BenJAMMIN@discussions.microsoft.com> wrote in message
> news:E7148F6F-C8E5-485C-80AB-0FC26C0E6D97@microsoft.com...
> > 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
> 
> 
> 
0
BenJAMMIN (21)
6/15/2004 3:24:01 PM
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" <BenJAMMIN@discussions.microsoft.com> wrote in message
news:51B75054-434C-4CA6-B86E-D613B6F6FA12@microsoft.com...
> 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" <BenJAMMIN@discussions.microsoft.com> wrote in message
> > news:E7148F6F-C8E5-485C-80AB-0FC26C0E6D97@microsoft.com...
> > > 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
> >
> >
> >


0
andyb1 (494)
6/15/2004 3:35:02 PM
OK. Try this:
=COUNTIF(B8:G24,"<>"&"")&"/"&102

-- 
Andy.


"BenJAMMIN" <BenJAMMIN@discussions.microsoft.com> wrote in message
news:21C40614-1867-4F95-AA26-81EDDF467391@microsoft.com...
> 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" <BenJAMMIN@discussions.microsoft.com> wrote in message
> > news:51B75054-434C-4CA6-B86E-D613B6F6FA12@microsoft.com...
> > > 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" <BenJAMMIN@discussions.microsoft.com> wrote in message
> > > > news:E7148F6F-C8E5-485C-80AB-0FC26C0E6D97@microsoft.com...
> > > > > 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
> > > >
> > > >
> > > >
> >
> >
> >


0
andyb1 (494)
6/15/2004 4:24:37 PM
Or, for the other way round:
=COUNTIF(B8:G24,"="&"")&"/&102"

-- 
Andy.


"Andy B" <andyb@takethisbitout.dawsons.co.uk> wrote in message
news:uUd22UvUEHA.4020@TK2MSFTNGP09.phx.gbl...
> OK. Try this:
> =COUNTIF(B8:G24,"<>"&"")&"/"&102
>
> -- 
> Andy.
>
>
> "BenJAMMIN" <BenJAMMIN@discussions.microsoft.com> wrote in message
> news:21C40614-1867-4F95-AA26-81EDDF467391@microsoft.com...
> > 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" <BenJAMMIN@discussions.microsoft.com> wrote in message
> > > news:51B75054-434C-4CA6-B86E-D613B6F6FA12@microsoft.com...
> > > > 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" <BenJAMMIN@discussions.microsoft.com> wrote in message
> > > > > news:E7148F6F-C8E5-485C-80AB-0FC26C0E6D97@microsoft.com...
> > > > > > 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
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>


0
andyb1 (494)
6/15/2004 4:29:39 PM
Reply:

Similar Artilces:

my auto reply error
I am attempting to establish an auto-reply (ooo) because we do not have an exchange server. It is not working and I receive a Rules in Error message window reading "cannot reply to message". However, that notice will only occur once. I am following the steps as described in the Outlook (2007) help menu for creating templates and rules for auto replies. Thanks, Hans ...

comments formatting
Excel uses Arial 10 pts in sheets and Tahoma 8 pts in comments by default. Is there a way to change the default comment point size to e.g. 10 or 12 pts? Can't find this setting ;-) Rob Hi Robert have a look at http://www.contextures.com/xlcomments02.html#Default Frank Robert wrote: > Excel uses Arial 10 pts in sheets and Tahoma 8 pts in comments by > default. > > Is there a way to change the default comment point size to e.g. 10 or > 12 pts? Can't find this setting ;-) > > Rob Rob, John Walkenbach has some info on this topic. http://www.j-walk.com/ss/exce...

formating date from general2 custom
I have an input xls file with the date but like this: Wed May 14 00:00:00 GMT-05:00 2008 I need to have just following format dd/mm/yyyy I tried to format it using Format->Cells->Date or Format->Cells- >Custom but this is failing. Nothing changed. Any ideas? Thanks for the help. Vlad <vladimir.vaynshtok@gmail.com> wrote in message news:1013b138-352b-4799-81ba-9508b74a1850@t54g2000hsg.googlegroups.com... >I have an input xls file with the date but like this: > > Wed May 14 00:00:00 GMT-05:00 2008 > > I need to have just following format > > d...

Leased autos
In the fixed asset module, one can pick the "type" of asset (new, used, leased). We have leased autos. Is anyone using the "leased" option & for what purpose? Can you tell me how you are using it. It might be a good way to track leased autos, but there is no asset cost to record. Thanks Merle A true lease is a long term rental agreement and not a purchase document. Most Leasing companies "abandon" the property to the lessee at the end of the term or sell it at a low disposal rate. Thus for leases, you have no asset value. If your "lease&quo...

Conditional formatting of one cell depending on other cell.
Hi !! Is it possible to format a cell conditionally depending on the value o another cell ? For eg. If P3 contains X as a data in cell then A3 changes to red & I P3 contains Y as data in cell A3 changes to blue, similarly if P contains Z as a data in cell then A3 changes to green -- Little Maste ----------------------------------------------------------------------- Little Master's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2822 View this thread: http://www.excelforum.com/showthread.php?threadid=48032 Whilst in cell A3, do:- Format / CF / change '...

MSG file format
We have a VB.NET app and we want users to be able to drag and drop email messages from their Outlook Inbox to this application. I am looking for documentation of the MSG file format so we can parse the email message once it is dropped onto our application. Can someone point me towards the documentation of the MSG file format? Or if there is a better way to read the object that is dragged and dropped onto our VB.NET app, let me know. Thanks Bill Reynen University of Illinois Foundation reynen@uif.uillinois.edu ...

CSV format
I notice that when I save as CSV (comma-delimited), Excel puts quotation marks around string fields only where they are necessary (e.g. those with embedded commas). It omits them otherwise. I am trying to export to another application where quotes are mandatory around every string field. Is there a way to force Excel to quote all strings when saving a CSV? I'm running Microsoft Offce Excel 2003 SP1 - Rich Take a look here: http://www.mcgimpsey.com/excel/textfiles.html#csvwithquotes In article <OJ4b$nhoFHA.3912@TK2MSFTNGP10.phx.gbl>, Rich Pasco <richp1234@hotmail...

custom format or formula?? a tough one
have cols of data (one would be percent change) where I would divide. I want all of the cells to display right aligned. I also want to change the "division by zero" result to be changed to a "-", and centered. I can do it when I want a dollar sign or no sign...with an if statment to change the div by zero and the accounting format. I want to it to occur on the percent columns as well, and can't figure out how...the "-" never comes out centered. I think I just need a custom format but can't seem to get it. Please help! .. ...

Sum column values that contains text &/or dates
Column D3 thru D43 contain a mixture of values & dates. For example: D3 = 16,026.76 D4 = 11-18-05 D5 = blank D6 = 6,855.34 D7= 11-03-05 D8 = blank D9 = 5,270.00 D10 = 11-02-05 Need sum to equal 28,152.10 (total only values from entry or formulas) Thanks so much. mikeburg -- mikeburg ------------------------------------------------------------------------ mikeburg's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24581 View this thread: http://www.excelforum.com/showthread.php?threadid=489114 If your dates are text strings which say "11-18-05"...

Auto-Categorization?
Is it possible to automatically assign categories based on certain criteria (like e.g. location)? Reason: I find it a bit tedious (and often forget) to assign proper categories. But since certain of these are redundant (e.g. with certain appointments already the location could automatically imply the category) it would be handy if OL could set the category automatically... Michael No... but you can use custom view to assign colors to items that are not categorized. So, in the Day/Week/Month view, all appointments happening at LocationA can be shown in Red and all appointm...

Drop Down box
Good luck understanding my confusing description below: I am making an invoice template ("InvoiceWorksheet") to be used fo multiple products from multiple manufacturers. I would like to select a product from a dropdown box A1 and have al subsequent fields automatically populate with information about m chosen product based on info from the "DataWorksheet" (a secon worksheet within this Excel file). _InvoiceWorksheet_setup:_ Cell A1 - I select BlueWidget from a dropdown. Cell A2 - automatically populates with BlueWidgetManufacturerName. Cell A3 - automatically populates...

Excel Number Formatting
I have a spreadsheet with a single column that contains numbers/text as follows: 1,2,2a,2b,3,4,5,5a,5b,5c,6 etc. When I go to sort the spreadsheet, the whole numbers are at the top (1,2,3,4,5 etc) and the numbers with letters are (in order) at the bottom). Obviously this ruins my spreadsheet because rows are now out of order. I know that this column needs to be formatted as "Custom" in the number formatting area so that Excel recognizes that there is a number with text, but for the life of me I can't figure out how to do it. The formats already listed don't work, ...

Format dollar amounts for check printing
I would like to format a number to print amount on a check. For example: $5,280.25 Format to: Five thousand two hundred eighty and 25/100 dollars Hi D, See: How to convert a numeric value into English words in Excel http://support.microsoft.com/default.aspx?scid=kb;en-us;213360 How To Convert Currency or Numbers into English Words http://support.microsoft.com/?scid=kb;en-us;259663 --- Regards, Norman "dford" <dford@discussions.microsoft.com> wrote in message news:1141CF13-A959-44FE-8DDA-06C56847A730@microsoft.com... >I would like to format a number to print amount...

Highlighting Every Other Row - Conditional Formatting
Hello, Here is my original post, which was very helpful: http://www.microsoft.com/office/community/en-us/default.mspx?dg=microsoft.public.excel.misc&mid=6ab8403b-96bb-4c7d-bfb5-a602d4127bde&sloc=en-us I am now using Office 2007. When I try to use the formula =MOD(ROW(),2) for highlighting every other row, it only highlights alternating BLANK rows. If the row is populated, it will not highlight it. Can someone help me figure out why this is happening & how to fix it? Thanks! NicoleS Perhaps it is more strongly typed than earlier versions. Try this formula inst...

How do I copy data in single cell format to a merged cell format
I need to copy a list of data in single cell format into a spreadsheet where those same columns are merged cells (with four cells merged into one). I have several hundred lines of data so some way of copy & paste (other than each cell individually) would be appreciated. Thanks Paul Hi Paul What I would do is copy format of sheet with merge cells to blank sheet then copy single cell data to sheet with merge cells select single cell to paste you lose formatting so after just copy format from blank sheet created earlier I find this easiest way Hope this helps Tina "Paul" w...

Cell formatting in Mail Merge
I have an Excel spreadsheet, showing dates when a succession of different stages are reached for each client in my conveyancing job. All dates are in UK format in Excel. However, when I mail merge in Word using this data source, all dates appear in Word as US format, and any empty cells are populated with 12:00AM. Could you please tell me if this is an Excel problem, and what the solution may be. Debra Dalgleish posted this to a similar question: There's an article on the Microsoft web site that might help you: Answer Box: Numbers don't merge right in Word http://office.mic...

auto complete replacement list
Is there a file that contains the auto complete data for Excel 03 I have allot of data to add and there must be a way to do it faster than going in through the auto complete options and adding them one by one. Thanks Tangy Auto Complete or Auto Correct? Auto complete or "pick from list" is based on what you have entered in contiguous cells above where you are now entering data. There is no list saved anywhere that you can edit. Auto Correct list is added to one by one AFAIK. Gord Dibben Excel MVP On Sun, 20 Feb 2005 16:54:16 -0500, "Tangy" <t a n g y@rogers...

Date Formats #4
I'm trying to change the date format from an American to an Australian format. I've tried to change the format in format/cells, but checking the AU option doesn't apply the changes and the example doesn't change as well. Any ideas? Thanks in advance Hi Sammy! Best may be to change your Regional Settings: Start > Control Panel > Regional options Change the country Review the date forms offered Apply For a one off situation you can always pre-format with the Australian date system by using Custom formats. I believe that the setting of locale in Format > Cells o...

Calculate total metric based on sum of metric over specific time f
I am trying to calculate the sum of specific metrics as they occur over a certain time period. You can find the spreadsheet I'm using here: http://www.staceyboyd.com/excel/example.zip (I had to put it in a zip file, otherwise the browser didn't seem to want to access it.) Basically, in the Total section (starting N5), I'm trying to sum that particular metric (in this example Impression estimates) that were posted during the month that appears to the in the M5-M11 cells). The first place you could actually even see a calculated summary would be N7 for Jan-2010....

Calculating the Sum IF fields equal criteria
I have expense fields classified as "parts" or "labor". I need to create a formula to look at ExpenseType fields 1-10, determine if they are labor, and then calculate the Total fields sum of the labor field expenses into a field called TotalLaborCost. Then I need to do the same process to calculate the sum of expenses classified as Parts in the TotalPartsCost field. Fields in the form: "ExpenseType1" "ExpenseType2"..... (type of expense: labor or parts) "Quantity1", "Quantity2"..... (how many of each item or how many hours) "C...

Auto-format in Microsoft Excel.
Each time i try to enter a number range, for example, 8- 10, in Excel, it constantly re-formats it to a date. If I change the formatting to "General" it turns it into a random number, usually 38209. I would like to turn off ALL auto-formatting, but that's probably asking too much. How do I disable this frustrating feature? thanks. Hi Paul When you enter "8-10" in a cell, that's not strictly a number but Excel thinks you mean a date so converts it as such. A date is a number and in your example the number 38209 represents August 10 2004 which is not a ra...

Unique entries based on condition
Hi, I'm really struggling with what I thought should be easy. A B C D branch salesperson transaction num Item code I'm trying to the number of unique saples people in each branch, the number of unique tranactions by each sales person, and the number of unique items in each transaction. In another program I use "Count Distinct" but am struggling to acheieve the same in excel. Any ideas? -- Rich http://www.rhodes-lindos.co.uk http://www.rhodes-pefkos.co.uk Try these array fo...

How to keep format when importing Excel into SQL.
Can anybody help me please? When I import Excel file into SQL, a field that formated as 0000000000 (custom), loosing leading zeros. I tried to change data type in SQL after importing. No luck. I appretiate any help. See if using IMEX=1 helps: http://pratchev.blogspot.com/2007/10/importing-excel-data-into-sql-server.html -- Plamen Ratchev http://www.SQLStudio.com Plamen, I tried this, but got an error: "The OLE DB provider "Microsoft.Jet.OLEDB.4.0" has not been registered." Thank you very much for looking into this. "Plamen Ratchev" wr...

Auto Archiving #2
I'm trying to change the Auto Archive setting for multiple (500+) users on a w2k server running as part of a Citrix farm. Currently default to c:\doc and settings.....etc Looked for reg key in current_user\soft\MS\WNT\CurrVer\Wmesssubsys\profile\*user name*\0a0d020000000000c000000000046 Didn't exist on the server's reg though. Help! Any idea's???? ...

how do I convert an excel spreadsheet to cif format?
How do I convert an excel spreadsheet into .cif format? This format is not available in the "save as" menu and I can't find an add in that will convert this for me. Can you suggest something? Thank you! What program is a .cif file used with? It's very possible that Excel supports this format but under a different name. For example, it may be a Comma Separated Values format (normally saved as ~~~.csv) but your program likes the extension .cif. Take a look at your program's documentation to see if you can determine what type of file the .cif is. You might als...