How to total cells in a range with data input

Greetings to all members

I am running Excel 97.

An office colleague presented me with an issue that might be of interest to
any Excel whiz and seasoned programmers.  I would certainly appreciate any
pointers in solving it.  So here goes.

The set of values includes 33 cells ranging from B4:D14.  The data type is
numeric.  Data is only input in a few cells.

QUESTION

What would be the function to enter in, say, cell A15 to indicate the total
number of cells in range B4:D14 that have received data input?  Or does it
involve some behind the scene VBA programming?

EXAMPLE

B6 = 9  C9 = 4  D11 = 0
Consequently, cell A15 should total 3, as only 3 cells have received data
input.  I hope I am making myself clear.  If not, please state so.

Thanks a million for any assistance.

Sylvie in Montreal


0
sdrsolo (3)
5/8/2004 3:24:05 PM
excel 39879 articles. 2 followers. Follow

4 Replies
595 Views

Similar Articles

[PageSpeed] 36

One way

=COUNT(B4:D14)

for numbers

=COUNTA(B4:D14)

for text and numbers

-- 

Regards,

Peo Sjoblom

"SylviBeOS" <sdrsolo@openface.ca> wrote in message
news:Oo9qb9QNEHA.1196@TK2MSFTNGP11.phx.gbl...
> Greetings to all members
>
> I am running Excel 97.
>
> An office colleague presented me with an issue that might be of interest
to
> any Excel whiz and seasoned programmers.  I would certainly appreciate any
> pointers in solving it.  So here goes.
>
> The set of values includes 33 cells ranging from B4:D14.  The data type is
> numeric.  Data is only input in a few cells.
>
> QUESTION
>
> What would be the function to enter in, say, cell A15 to indicate the
total
> number of cells in range B4:D14 that have received data input?  Or does it
> involve some behind the scene VBA programming?
>
> EXAMPLE
>
> B6 = 9  C9 = 4  D11 = 0
> Consequently, cell A15 should total 3, as only 3 cells have received data
> input.  I hope I am making myself clear.  If not, please state so.
>
> Thanks a million for any assistance.
>
> Sylvie in Montreal
>
>


0
terre081 (3244)
5/8/2004 3:22:10 PM
Sylvie,

If I understand you correctly try =COUNT(B4:D14)

If the cells may have text as well then use COUNTA(B4:D14)

HTH

Sandy

-- 
to e-mail direct replace @mailintor.com with @tiscali.co.uk


"SylviBeOS" <sdrsolo@openface.ca> wrote in message
news:Oo9qb9QNEHA.1196@TK2MSFTNGP11.phx.gbl...
> Greetings to all members
>
> I am running Excel 97.
>
> An office colleague presented me with an issue that might be of interest
to
> any Excel whiz and seasoned programmers.  I would certainly appreciate any
> pointers in solving it.  So here goes.
>
> The set of values includes 33 cells ranging from B4:D14.  The data type is
> numeric.  Data is only input in a few cells.
>
> QUESTION
>
> What would be the function to enter in, say, cell A15 to indicate the
total
> number of cells in range B4:D14 that have received data input?  Or does it
> involve some behind the scene VBA programming?
>
> EXAMPLE
>
> B6 = 9  C9 = 4  D11 = 0
> Consequently, cell A15 should total 3, as only 3 cells have received data
> input.  I hope I am making myself clear.  If not, please state so.
>
> Thanks a million for any assistance.
>
> Sylvie in Montreal
>
>


0
sandymann (252)
5/8/2004 3:35:28 PM
Thank you for a speedy reply, problem solved.  I will refrain from
cross-posting.
Sylvie


"Peo Sjoblom" <terre08@mvps.org> wrote in message
news:eJcL8ARNEHA.3556@TK2MSFTNGP09.phx.gbl...
> One way
>
> =COUNT(B4:D14)
>
> for numbers
>
> =COUNTA(B4:D14)
>
> for text and numbers
>
> --
>
> Regards,
>
> Peo Sjoblom
>
> "SylviBeOS" <sdrsolo@openface.ca> wrote in message
> news:Oo9qb9QNEHA.1196@TK2MSFTNGP11.phx.gbl...
> > Greetings to all members
> >
> > I am running Excel 97.
> >
> > An office colleague presented me with an issue that might be of interest
> to
> > any Excel whiz and seasoned programmers.  I would certainly appreciate
any
> > pointers in solving it.  So here goes.
> >
> > The set of values includes 33 cells ranging from B4:D14.  The data type
is
> > numeric.  Data is only input in a few cells.
> >
> > QUESTION
> >
> > What would be the function to enter in, say, cell A15 to indicate the
> total
> > number of cells in range B4:D14 that have received data input?  Or does
it
> > involve some behind the scene VBA programming?
> >
> > EXAMPLE
> >
> > B6 = 9  C9 = 4  D11 = 0
> > Consequently, cell A15 should total 3, as only 3 cells have received
data
> > input.  I hope I am making myself clear.  If not, please state so.
> >
> > Thanks a million for any assistance.
> >
> > Sylvie in Montreal
> >
> >
>
>


0
sdrsolo (3)
5/8/2004 3:47:52 PM
My pleasure Sylvie,
cross posting is OK IMHO but multiposting is not.. The big difference is
that with cross posting
the person who answer can see that it has been posted in several NGs and
he/she can also see any other answers.
Multiposting will leave no such trace and it will also confuse Google. It
doesn't matter much if there is a
question that takes 5 seconds to answer but if I spend 30 minutes to come up
with a fairly time consuming
answer it is not fun to know it has been answered in 2 other NGs. It's a
waste of resources..

-- 

Regards,

Peo Sjoblom

"SylviBeOS" <sdrsolo@openface.ca> wrote in message
news:%235pOuKRNEHA.1312@TK2MSFTNGP12.phx.gbl...
> Thank you for a speedy reply, problem solved.  I will refrain from
> cross-posting.
> Sylvie
>
>
> "Peo Sjoblom" <terre08@mvps.org> wrote in message
> news:eJcL8ARNEHA.3556@TK2MSFTNGP09.phx.gbl...
> > One way
> >
> > =COUNT(B4:D14)
> >
> > for numbers
> >
> > =COUNTA(B4:D14)
> >
> > for text and numbers
> >
> > --
> >
> > Regards,
> >
> > Peo Sjoblom
> >
> > "SylviBeOS" <sdrsolo@openface.ca> wrote in message
> > news:Oo9qb9QNEHA.1196@TK2MSFTNGP11.phx.gbl...
> > > Greetings to all members
> > >
> > > I am running Excel 97.
> > >
> > > An office colleague presented me with an issue that might be of
interest
> > to
> > > any Excel whiz and seasoned programmers.  I would certainly appreciate
> any
> > > pointers in solving it.  So here goes.
> > >
> > > The set of values includes 33 cells ranging from B4:D14.  The data
type
> is
> > > numeric.  Data is only input in a few cells.
> > >
> > > QUESTION
> > >
> > > What would be the function to enter in, say, cell A15 to indicate the
> > total
> > > number of cells in range B4:D14 that have received data input?  Or
does
> it
> > > involve some behind the scene VBA programming?
> > >
> > > EXAMPLE
> > >
> > > B6 = 9  C9 = 4  D11 = 0
> > > Consequently, cell A15 should total 3, as only 3 cells have received
> data
> > > input.  I hope I am making myself clear.  If not, please state so.
> > >
> > > Thanks a million for any assistance.
> > >
> > > Sylvie in Montreal
> > >
> > >
> >
> >
>
>


0
terre081 (3244)
5/8/2004 4:11:15 PM
Reply:

Similar Artilces:

Correct Currency Data Type and Validation Coding
Any suggestions are so greatly appreciated as I am really really stuck Using Access 2003 on XP OS My Access and VBA experience is limited. I have a several fields that I am checking for validation as currency. I have two questions 1) what is the best data type for currency. Currently I am using Table Data Type = Number. (I have tried other types with no luck) Field Size = Single Format=Currency. On the form I am formating to currency. 2) I want to be able to check for whole numbers and for those that are entered with a decimal. Currently I am using the below. Pri...

Reference Column of Named Cell vba
I have named a cell on sheet - how doe I reference just the column property of that named cell in vba on another sheet in the same workbook please ? Thanks Dim myCell as range set mycell = worksheets("somesheetname").range("SomeRangeName") Then you can use something like: msgbox mycell.column Isis wrote: > > I have named a cell on sheet - how doe I reference just the column property > of that named cell in vba on another sheet in the same workbook please ? > > Thanks -- Dave Peterson Dave Peterson <petersod@verizon...

how do i get the data in a chart to automatically update?
I have several charts tied to a large spreadsheet. The data in the charts don't automatically update. How do I get these to update without actually redoing the formulas for each source data?> ...

cells #2
hi, How do I restrict a word to a cell no matter how long the word is? I want the cell to expand in its row rather than spill over to the other cells. I also do not want to increase the width of the cell. Thank you in advance. Yem Click on <Format><Cells><Alignment><Wrap text> That will wrap text within a cell and increase the height of the cell. To do this click on <Format><Row><Autofit> Regards. Bill Ridgeway Computer Solutions "Yem" <Yem@discussions.microsoft.com> wrote in message news:E56B6BDE-64A9-4439-8048-7B12B9B882...

cell format: adding custom formats
I am trying to create a custom format that accepts numerical and Letter entries. I can create a custom format that allows numerical additions but not letters. For example, I went into custom formats and created this: "06405P1"0 Thus, if in the cell I type 6 I get 06405P16 This is correct. If in the same cell I type N6 I get N6 I do not get 06405P1N6 which is what I would like to have. Any help is appreciated. right click sheet tab>view code>insert this. works in col A below row 2 Private Sub Worksheet_Change(ByVal Target As Range) If Target.R...

Aggregating Data in Other Category on Pie Chart
Excel 2007 Basic Question ---------------- Is there a way to make a pie chart ignore "empty" cells? Note that the cells are not truly empty. The display of data in the cells has simply been "blanked" out using this formula: =IF(B19<1000,"",B19) More Information ------------------ I'm putting together a pie chart that will be updated monthly. There are 40 categories of data, and the values for those categories range from 1 ot 65,000. Obviously, this presents a problem in that the categories with low values will be shown as pie slices so thin as to...

Sample Access Database using Oracle Data and needing Time/Date Sta
Does anyone know of a good web site out there that has some sample Access Databases that invoke and use Oracle Data or even SQL Server for that matter that also uses a Time/Date Stamp driven Map to drive and filter a query to get results back via an ADO call??? I have been asked to create an Access Database as a GUI Type application which will utilize an Access Form and require User Input to Enter a Date and Time which will ultimately filter the data. I have no idea where to begin on something like this. (Boy....that sounds like a tall order....) Any help would be GREATLY...

Using User input in excel VBA
I'm supposed to make an invoice of 4 items. Each item needs to have user input for the description, price, and quantity ordered. Then I need to calculate the cost of the 4 items. Then calculate the sales tax at 6%. Then calculate the total cost including sales tax. This needs to be done using VBA code in excel. --I'm curious as to how to prompt the user to give me information. --I'm curious as to how I would use the users input in another cell once they entered it. -- I'm curious also as to how VB will know that something is entered once the user enters something into t...

totals in a pivot table chart
Hi I want to show the totals in a pivot table chart How can I show that By the way, I have the office 2000 Thanks so muc Sylvio I don't know of any way to include the totals in a PivotChart. You could create a normal chart, based on the pivot table, and include the totals in that. On Jon Peltier's site, there are instructions for creating a normal chart from pivot data: http://www.peltiertech.com/Excel/Pivots/pivotcharts.htm Sylvio wrote: > Hi, > > I want to show the totals in a pivot table chart. > > How can I show that? > > By the way, I have the...

Importing Data from Backup
I'm trying to bring in my contacts and calendar from my old computer to my new one, and have copied the data into a file and put in on cd. The file is an Outlook Data File as confirmed by Explorer. When I copy the file into the Local Settings-->Microsoft-->Outlook folder, it appears there as having the same file size. But when I try to open it within Outlook, either through the import function, or simply file-->Open Data File, nothing happens, and it simply creates a new personal folder with nothing in it. I know that it copied the data because I watched while it said copying ...

Cell referencing 12-14-09
I have a sales\stock level related problem. Each row on the worksheet represents a sales day. Column A contains the previous days sales figure, a list of numbers in the range 1 to 20. Column B is the daily stock level which starts at 100 (cell B1) and decreases each day by the previous days sales figure ( the adjacent A cell). Column C is a list of the number of days it takes to deliver the items when they are re-ordered and will be in the range 1 to 4 (days). If the stock level (column B) goes below 11 then a re-order (which is 100 items) is placed and arrives in x number of d...

Shape Data versus Properties?
Is there a difference between Shape Data (found by right-clicking a shape, highlighting "Data" and then clicking "Shape Data") and Properties (found by right-clicking a shape and then clicking "Properties")? Thanks. Jason Hello Jason, No, it's just a different way to access the same data. Best regards John John Goldsmith www.visualSignals.co.uk "Jason Freeman" <loosus@coastalnet.com> wrote in message news:uKvOLNowHHA.840@TK2MSFTNGP03.phx.gbl... > Is there a difference between Shape Data (found by right-clicking a shape, > hi...

Formula or string
In a cell where format is set as Currency, I enter 3+5 and it gives a string, in stead of the calculation result (8 in this example). I have to enter the equal sign first: = 3+5, then this accept it as a formula, showing a green triangle in upper-left corner. Is there a default setting we can change this? So I do not need to type = on each cell entry. Thanks. The "=" sign lets Excel know you've entered a formula and it needs to calculate that formula. That's a basic operating principle of a spreadsheet, a way of defining what is and is not a formula. The "=&qu...

Variable range reference
How do I use the value of a cell as a reference to a range? I have a cell value that is assigned by a series of IF statements. I would like to use the value of this cell within a INDEX and MATCH function to define the range. =indirect() ??? =index(indirect(a1), ....) if A1 contained something that looked like an address. Kyle Szukaitis wrote: > > How do I use the value of a cell as a reference to a range? > > I have a cell value that is assigned by a series of IF statements. I would > like to use the value of this cell within a INDEX and MATCH function to > defin...

Autorecovery & Word Work Files in Microsoft User Data folder. Safe to delete?
In my home folder/Documents/Microsoft User Data folder, there are tons of Autorecovery Files (eg., "Autorecovery Save of Exercise Journal.doc," "Autorecovery Save of Budget.doc," etc.) These are files I haven't modified for several months or even since last year. In addition, there are also lots of Word Work Files with more cryptic names like "Word Work File A_5," "Word Work File A_1496285709," etc. Can I safely delete these Autorecovery files and Word Work Files from my Microsoft User Data folder? In article <C208D725.5B72%delphinshire@yahoo....

Double lines of text in one cell
How can one generate double line of text that is shrunk to fit into one cell? Currently with word wrap text goes out of view. Any help would be much appreciated. While entering text in a cell pressing Alt and Enter will force the text into two lines. Charlie O'Neill >-----Original Message----- > How can one generate double line of text that is >shrunk to fit into one cell? Currently with word wrap text >goes out of view. > >Any help would be much appreciated. >. > Gary With wrap text set on, Format>Row>Autofit. Note: you can break your lines...

'access data on web'
With prior versions of Money, synching w/ the web would (apparently) simply copy data from the Money file to a page behind your login at MSN. I appears that has changed. It looks like MSN is itself going out and downloading information from financial institutions and then that information is updating my Money file. What is actually happening when I elect to make my data accessible on the web? I just want to have my Money stuff copy to my MSN login...is that possible? IF you don't enable transaction download from an FI that is only accessible via a "third party" (Yodlee ...

How do I preserve text color when combining cell data
I'm using "&" to combine data from multiple cells. Each cell has text of a different color and I want to preserve those colors in the new combined cell. The new combined cell is formatting all the text to one color. Anyone have any suggestions on how to maintain the separate colors in the new cell? A formula can only return a value to a cell, so you cannot return formatting and such. =A1&A2 will return the value of A1 and A2 to the cell that has that formula entered in it. HTH Regards, Howard "CraigS" <CraigS@discussions.microsof...

Create template that ignors data list rules
Question: Is there a way to copy all rows in a worksheet [template] to a master data list at the same time, and have possibly several users saving their worksheet forms simultaneously with the data list being updated automatically as they save their work or is there a better way to accomplish this task using Excel? Please explain like I'm a two year old. The plan is for several users on a network to enter data in an Excel 2002 form [1 worksheet each user] the form contains about 17 rows with 10 columns on a template. When the users save their form the data will be automatically copied ...

Error: "too many different cell formats"
I am working with a large model (25MBs) and when I try to make formatting changes I have started to get the error message: Error: "too many different cell formats" What is causing this? Is there a quick fix? The Microsoft support site discusses this error here: http://support.microsoft.com/default.aspx?scid=kb;en-us;213904 I encountered this error on a large spreadsheet that I inherited from another user, who had a number of "back office" or "overhead" tabs hidden in the sheet. Even though these sheets were for VLOOKUP purposes and the user never saw them, t...

One range inside another, Intersect
After I had the same situation, and reading some posts about checking to see if the cells in one range were contained in another range, I wrote the function below. It returns the boolean True or False value, and optionally, if range A is not entirely within range B, the first failing cell is returned. Public Function bRngAinRngBF(RngA As Range, RngB As Range, _ Optional FailedRng As Range = Nothing) As Boolean 'True if every cell in RngA is in RngB. > 1 Area is OK in either. 'When False, the 1st cell in RngA not found in RngB is returned in FailedRng. Dim Ce...

MAX Value with Describing Cell
I have a table of date/time information in one column and temperature info in another. I am trying to use a formula to pull the highest and lowest temperature (pretty easy using MAX / MIN), but can I reference the previous column to report the date/time information that record temperature was recorded? I realize now that the below example doesn't work because the MAX doesn't return a cell reference, but an actual value. Is there any way to get the actual cell that the MAX function locates? "Deuce Sapp" <dssapp@atlasmachine.com> wrote in message news:OkmtmWYcDHA.1...

Using cell content as reference in formula
I am a beginner with Excel, and a little lost. When creating a formula, is it possible to use the content of a cell, without creating labels, to refer to the cell, or group of cells in which the content appears? For example, in countless places in my spreadsheet, the text "Total" appears in a cell, refering to a numeric value, 4 columns to the right in the same row. I would like to create a formula that adds all the values, wherever they appear 4 columns to the right of the cell containing the text "Total". To take this one step further, I would like to create a secon...

How do i change the location where the input message appears
I have managed to get the input message to appear in a set place, which is where I want it to stay, however whenever I close the spreadsheet it reverts to appearing in the lower right corner of the cell. How can I get it to stay where it is? I don't think there's a way. Kegs wrote: > > I have managed to get the input message to appear in a set place, which is > where I want it to stay, however whenever I close the spreadsheet it reverts > to appearing in the lower right corner of the cell. How can I get it to stay > where it is? -- Dave Peterson ...

total memory available is only 3.3GB with 4GB physical memory under windows 7 64 bit 11-29-09
I had 4GB physical memoryon the PC. With Windows vista 32 bit, available memory is shown as 3.3GB. I asked and was told that only under 64 bit windows, will the available memory be 4GB. When I upgraded to windows 7, I chosed 64 bit, but the available memory shown in the system is still 3.3 GB. Why is this and is there a way to make the full 4GB available? It's possible that my video card is using RAM instead of its own RAM. If I install a video card with its own RAM, will the full 4GB be available? Thanks. Little has changed in this respect in Windows 7. Starting wit...