Count Text Values in Column

I converted a column of number cells to text to allow entry of 1a, 1b, etc. 
and need to be able to count the totals of these cells.  I can't give it 
specific criteria to use DCOUNTA since the text value will vary.  Is there a 
way to get a count of non-blank cells in a column of text values?

Thanks! 


0
karlspam (74)
8/1/2005 3:33:09 AM
excel.misc 78881 articles. 5 followers. Follow

7 Replies
466 Views

Similar Articles

[PageSpeed] 34

As a note, I do not really care what the value is, I just need to count the 
totals.  If there is a way to reformat or remove text via a formula, that 
would work as well.  Thanks!

"Karl Burrows" <karlspam@spam.yourbeacon.com> wrote in message 
news:OrvawmklFHA.1416@TK2MSFTNGP09.phx.gbl...
I converted a column of number cells to text to allow entry of 1a, 1b, etc.
and need to be able to count the totals of these cells.  I can't give it
specific criteria to use DCOUNTA since the text value will vary.  Is there a
way to get a count of non-blank cells in a column of text values?

Thanks!



0
karlspam (74)
8/1/2005 3:40:47 AM
Hi,

If you want a count of non-blank cells in a column of text values use 
=COUNTA(range)

Regards

Govind.

Karl Burrows wrote:

> I converted a column of number cells to text to allow entry of 1a, 1b, etc. 
> and need to be able to count the totals of these cells.  I can't give it 
> specific criteria to use DCOUNTA since the text value will vary.  Is there a 
> way to get a count of non-blank cells in a column of text values?
> 
> Thanks! 
> 
> 
0
adgraj1 (46)
8/1/2005 3:42:02 AM
Will COUNTIF() work for you?

0
CycleZen (674)
8/1/2005 3:44:17 AM
The empty cells are not really blank, they are pulled from a database 
worksheet using a formula, so their value is "".  I tried it and it counted 
all the cells as having a value.

"Govind" <adgraj1@indiatimes.com> wrote in message 
news:F4hHe.8$GA1.95@news.oracle.com...
Hi,

If you want a count of non-blank cells in a column of text values use
=COUNTA(range)

Regards

Govind.

Karl Burrows wrote:

> I converted a column of number cells to text to allow entry of 1a, 1b, 
> etc.
> and need to be able to count the totals of these cells.  I can't give it
> specific criteria to use DCOUNTA since the text value will vary.  Is there 
> a
> way to get a count of non-blank cells in a column of text values?
>
> Thanks!
>
> 


0
karlspam (74)
8/1/2005 6:10:23 AM
"Karl Burrows" wrote
> .. The empty cells are not really blank, they are pulled from a database
> worksheet using a formula, so their value is "".  I tried it and it
counted
> all the cells as having a value.

Perhaps try something like, in say, B1:
=SUMPRODUCT((--(A1:A10<>"")))

Adapt the range to suit, but note that
we can't use entire col references in SUMPRODUCT
--
Rgds
Max
xl 97
---
GMT+8,  1� 22' N  103� 45' E
xdemechanik <at>yahoo<dot>com
----


0
demechanik (4694)
8/1/2005 8:19:21 AM
Try this:

=COUNTIF(A:A,"*?")
-- 
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



"Karl Burrows" <karlspam@spam.yourbeacon.com> wrote in message
news:OrvawmklFHA.1416@TK2MSFTNGP09.phx.gbl...
> I converted a column of number cells to text to allow entry of 1a, 1b,
etc.
> and need to be able to count the totals of these cells.  I can't give it
> specific criteria to use DCOUNTA since the text value will vary.  Is there
a
> way to get a count of non-blank cells in a column of text values?
>
> Thanks!
>
>

0
ragdyer1 (4060)
8/2/2005 12:08:11 AM
That is perfect!!!  Excellent!

What does the "?" do to the formula?  I can see using the wildcard to pickup 
anything that is text.  Does the "?" just tell it to look at an unlimited 
size field?

"RagDyer" <RagDyer@cutoutmsn.com> wrote in message 
news:ObDdyZvlFHA.3552@TK2MSFTNGP10.phx.gbl...
Try this:

=COUNTIF(A:A,"*?")
-- 
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



"Karl Burrows" <karlspam@spam.yourbeacon.com> wrote in message
news:OrvawmklFHA.1416@TK2MSFTNGP09.phx.gbl...
> I converted a column of number cells to text to allow entry of 1a, 1b,
etc.
> and need to be able to count the totals of these cells.  I can't give it
> specific criteria to use DCOUNTA since the text value will vary.  Is there
a
> way to get a count of non-blank cells in a column of text values?
>
> Thanks!
>
>


0
karlspam (74)
8/3/2005 3:23:18 AM
Reply:

Similar Artilces:

ClistCtrl > How to auto-adjust columns size ?
Hi all, I have a CListCtrl in report view, two columns, hidden headerCtrl. I populate it with some items (less than 10). I'd like to programmatically auto-adjust the columns width according to their content, exactly like pressing Ctrl+'+' on a ListCtrl. Any idea ? Thanks, Dansk "Dansk" <dansk@laouilest.com> wrote in message news:e1x4VF9wHHA.276@TK2MSFTNGP06.phx.gbl... > Hi all, > > I have a CListCtrl in report view, two columns, hidden headerCtrl. > I populate it with some items (less than 10). > > I'd like to programmatically auto-adjus...

How do I delete a text box from an Excel spreadsheet?
Rightclick on it and select cut If the textbox is from the control toolbox toolbar, you may have to click on the designmode icon first. Uncle Binky wrote: -- Dave Peterson ...

Category color changes when changing values.
When I copy a chart I get two identical ones. When I change the values of one of the charts and sort the values, Excel changes the colors of the categories to a preset order, so that the color of the biggest customer in chart 1 is not the same as this same customer (let's say now on the third place) in chart 2. Is there a way to prevent this? ...

Changing text size
Hi, We enter all our customers in our database in all caps (text) for the names, and addresses. But when you put in the zip code and the database auto finds the town and state it is put in upper, and lower case. Is there a way to change this default to all caps for the auto fill. I am experienced in editing xml. files ( just a note) Any help would be appreciated.....Dennis No, there's no way to change that in the front end, that's something that is happening at display time and is meant to make the data more readable. You could enter a suggestion on Customer Sou...

Changing the Tooltip text in an SDI Toolbar
I've been trying to find a way to change the tootip info for a toolbar. Currently, I've tried getting the toolbar CToolBarCtrl and CToolTipCtrl objects but haven't been able to figure out what to do with them. Any help would be appreciated. Thanks, Ken If you are talking about tooltip info of a particular button on a toolbar, change it in resource editor by double clicking on the button and changing the text that appears at the bottom of the dialog. Text that appears before \n is tootip. -- Ajay Kalra [MVP - VC++] ajaykalra@yahoo.com "Ken Slight" <kslight@char...

importing multiple text files???
Hi, I am experiencing a big problem. As a part of my analysis for PhD I have to analyze more then 1000 files. The data that I have is in text files. To be more precise, I have 5 different data sets, from different meteo centres, each centre has a data set of 365 files for each day during the year. The format of text files is something as following: Camborne Met. Office WINDS rev 4.1 50.20 -5.30 88 02 01 01 00 00 23 0 29 3 35 07:09 (3.0) 07:09 (3.0) 06:08 (3.0) 284 284 65 65 400 400 23 23 12.5 12.5 1 1500 1500 35 35 400 400 43 90.0 43 74.5 313 74.5 HT SPD DIR Radials... 0.101 9.8 113 0.0...

Access ODBC problem: Excel cannot get float columns
I need some help I'm trying to get Access external data from an Excel workbook, using the MS Query feature. Everything is ok except when I try to fetch some tables that ODBC refuses to get data from. The error message tells that the MS Access ODBC driver doesn't allow some columns to be transmited because of its number of characters. The most strange thing is that I can see the data from MS Query correctly, but I caannot get it back from Excel. After some tries, I thing it occurs only with real typed columns. Can anybody help me? I use Windows XP Home + Office 2000 spanish versio...

How can I Enable a Check Box based on another fields value?
Hi There, I have a form with a disabled check box. I need to enable it when a certain value ("approved") is selected from a combo box. When I am in Form Design View and I have the Check Box selected the Conditional Formatting menu item on the Format menu is greyed out. I am using Access 2003. Can you tell me what I need to do to make this work? Many thanks, David As you've discovered, Conditional Formatting isn't availabe to checkboxes. Try this: Private Sub Form_Current() If Me.YourComboBox = "Approved" Then YourCheckBox.Enabled = True Else YourChe...

Newbie: can't get a calculated value on the form?
I have a table with numbers and a form that shows the numbers. I have a query that takes one of the numbers and mulitplies it. I put a text box on the form from the query result field, but I get a "#Name" error instead of the result. When I run the query, I get the correct result. Help, please? Ed "Ed from AZ" <prof_ofwhat@yahoo.com> wrote in message news:d1e7d27c-11d9-4696-8d19-4c5fdd9dbb89@d70g2000hsb.googlegroups.com... >I have a table with numbers and a form that shows the numbers. I have > a query that takes one of the numbers and mulitplies it. I p...

Converting Values to Unicode Characters
The function CHAR converts a value in the range 1-255 to to an ANSI character. Is there a way to convert values in the range 1-65,342 to Unicode characters? -- Gary L. Smith gls432@yahoo.com Columbus, Ohio You can use VLOOKUP but you'll have to create your own lookup table of the unicode characters and I think you'll have to paste it onto the same spreadsheet.... Maybe you can find a lookup table on the internet somewhere that you can easily paste into your spreadsheet. tsides <tsides@intelligentsystemsconsulting.com> wrote: > You can use VLOOKUP but y...

Graph Data Values
I am trying to create a pick and mix graph that shows forecast spen against actual spend by a selected business area. I somehow want to create a graph on the fly based on the selections fo example: If I choose company one and want to see the contracted data I want th graph to pick up the forcasted contracted Labour, Passthru and Othe and show the combined contracted actuals; See attached; and if i then choose company 2 then graph will pick up the releven ranges? HELP!! Attachment filename: help.zip Download attachment: http://www.excelforum.com/attachmen...

Counting Question #3
I have three columns: dates, values, and names 10/17 $300 Jim 10/17 $300 Jim 10/17 $200 Tom 10/17 $100 Jim When I enter Jim and $300 in to two separate cells, in a third cell I want to count the number of "days" it applies to (all the way down the spreadsheet.) So in other words, there are two instances in the same day of Jim and $300, but since it all happened on one day, the answer would be one. Hope I explained it well. =sumproduct(--(b1:b10=x1),--(c1:c10=x2)) where x1 holds the amount, x2 holds the name and B1:B10 holds the list of amounts and c1:c10 holds the list of...

Passing Values from One Form to Another Including a Combo Box
Hi, hope someone can help with passing two values from one form to another by way of a command button. I have spent a week on various code taken from this site, but still no luck. Please ... someone help!! The form I am passing values from is called PATIENT HISTORY-Form. On this form, I need to pass a date from a field called DateSFESigned and I also need to pass information collected from a Combo box, Combo91. The command button is called Command119. The form that the values are being passed to is called Personal Habits- Form. Thank you in advance for any help on this matter. Maurita ...

sumproduct--counting--zero--blank cells
I'm using these formula to count, =SUMPRODUCT(($W$9:$W$272>=0)*($W$9:$W$272<10)) =SUMPRODUCT(($W$9:$W$272>=10)*($W$9:$W$272<20)) ........etc how do i get it so bank cells are excluded from the count. The way it is now, they are counted in the 0 to 10 range... Thanks Jeremy -- Message posted via http://www.officekb.com COUNTBLANK(range) "jeremy via OfficeKB.com" wrote: > I'm using these formula to count, > > =SUMPRODUCT(($W$9:$W$272>=0)*($W$9:$W$272<10)) > =SUMPRODUCT(($W$9:$W$272>=10)*($W$9:$W$272<20)) > ........etc > how do...

Counting blanks as zeros
Column AZ contains zeroes as well as blank cells (meaning no value has been entered in the cell). In my formula below, I want to reference only the cells that contain zero and ignore the cells that are blank. As written, the formula is referencing both zero and blak cells. How can I modify the formula to do ignore the blank cells in column AZ? {=SUM(IF(Chart1!$A$2:$A$10000=A3,IF(Chart1!$C$2:$C$10000=B3,IF(Chart1!$AZ$2:$AZ$10000=0,Chart1!$F$2:$F$10000),)))} Thanks, Bob You can add one more condition Chart1!$AZ$2:$AZ$10000<>"" or use SUMPRODUCT() formula as...

45 Degree Angled Text & Fill Option
I have Excel 2003 (11.6355.6360) running on XP and I'm wondering if this is a bug or not. If you have text in the first Row and you set it to 45 degrees angled, if you try and fill different cells with different fill colors, sometimes the box will fill angled, othertimes straight up and down. As a simple test, try creating a blank worksheet and make the first 3 columns 35 each in width and 100 in height. The type some text in each of the cells - not too much. Now select all 3 cells and format their alignment to 45 degrees. Now pick each one of the cells and fill each with a different ...

stationary column on a spreadsheet
I have a spreadsheet that i want to make the first column not move when i scroll to the right. And one teach a dummy. Select cell B1 Choose Window > Freeze Panes wc4ew wrote: > I have a spreadsheet that i want to make the first column not move when i > scroll to the right. And one teach a dummy. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html ...

Text in pivot table limited to 255 characters
I am using a pivot table as an efficient way to aggregate text responses from a large data set. However, the pivot table cuts off the text after the first 255 characters (similar to when you copy a worksheet by using the move/copy option). How can I overcome this? I have tried putting the pivot table on the same sheet as the dataset, but that does not work. I should also note that these pivot tables are then fed into an automated report through a complicated set of VLOOKUPs, etc. The pivot table aggregates several questions and responses from many areas of the datset into one discr...

Count Text Data
Using 2007 on Vista If I've got text data which in some columns either has data or there is a blank, what formula do I use to count how many cells have text in them per column? Many thanks =COUNTA(A2:A200) will count everything except blanks post back if you have numbers as well that should not be counted -- Regards, Peo Sjoblom <weewillie@anon.com> wrote in message news:lfm764d39m4prld1iiqllpjuen7a1eptoq@4ax.com... > Using 2007 on Vista > > If I've got text data which in some columns either has data or there > is a blank, what formula do I use to count...

Count if based on 2 criteria
I am attempting to summarize some data based on the values in 2 different cells. Example Count the number of rows where column A = xyz and column U = "this is a test" I know the countif statement can't do multiple criteria, but is it possible to use nested countif statements, or use some combination of AND or IF statements? Thank you Answered in microsoft.public.excel.worksheetfunctions. Please do not post the same question separately to multiple newsgroups. It fragments the thread, and leads to people wasting time constructing answers to questions that have already be...

count data in column
Hi, I am using excel97 and trying to create a chart that has 5 columns of data in it a,b,c,d,e. I an trying to make a chart only for certain data in column a and column d. The data that I key off of is in column d and begins with s/ how can I count the number of s/ in column d? how can I create a chart that shows both and only that data that begins with s/ and the data in column a? --- Message posted from http://www.ExcelForum.com/ In cell F2 (I assume row 1 has headers) enter this formula: =LEFT(D2,2) and fill it down as far as you need. select any cell in the table, and apply an au...

Cycle Count
Hi, Client running GP 10. I am setting up their cycle count schedules and have run into an issue that I can't get an answer for. Want to set up the count to give me the following quantity of items to count weekly: A - 15 Items B - 10 Items C - 5 Items I am unable to find a way to automate this. Any suggestions (besides buying other count software?). -- Jim Lines Sr. Microsoft Dynamics GP Applications Consultant Certified Microsoft Dynamics GP Specialist I don't think so Jim. The assumption behind cycle counting is that you'll count all your inventory at least once annual...

How can you get Column graphs to be next to each other instead of spaced apart?
Thanks You are welcome. :-) -- David Biddulph <simonlavender@gmail.com> wrote in message news:1926b4aa-693f-41fa-a986-1aca28eb78b7@j1g2000yqi.googlegroups.com... > Thanks Hi Simon, If you just want the columns closer together, select the series, > Format data series > options, and reduce the the Gap Width. Dave "simonlavender@gmail.com" wrote: > Thanks > ...

Counting the number cells between two dates
Hi guys, Hope someone can help with this, I'm pretty sure it'll be quite a simple one. Column A:A contains a list dates, I want to use a formula to count the number of cells which contain a date between 01/01/05 - 31/01/05. Any ideas, Many thanks, Dave Try: =SUMPRODUCT((A1:A1000>=--"1/1/05")*(A1:A1000<=-- "1/31/05")) BTW - I'm using American date formats in mine. HTH Jason Atlanta, GA >-----Original Message----- >Hi guys, > >Hope someone can help with this, I'm pretty sure it'll be quite a simple one. > >Column A:A con...

Replace the column letters with my own heading
I want to know how i can i replace the column letters so i can put in my heading so that i always know which coumn i'm in and which question i'm answering when i'm entering data further down the page. Or any way that i can get my headings to follow down the page as i'm entering data so i can always see it no matter how many rows down i am. If your headings are in row 1 Click in cell A2 and Goto Window>Freeze Panes. Freeze Panes freezes anything above and to the left of the active cell. HTH Martin "Catter77" <Catter77@discussions.microsoft.com> wr...