#### count or sum

```how can I count all the occupied cells in the same column
if the cells contain text and numbers?
A
row 1   br1
row 2
row 3
row 4   cb2
row 5
row 6
row 7   df6
ect down to row 200.

the answer should be 3 (the number of cells occupied)

rgs  jerie

```
 0
anonymous (74722)
3/5/2005 6:39:37 AM
excel.misc 78881 articles. 5 followers.

5 Replies
686 Views

Similar Articles

[PageSpeed] 35

```use:

=COUNTA(A:A)

Regards

Trevor

"jerie" <anonymous@discussions.microsoft.com> wrote in message
news:4a1c01c5214e\$19ffd710\$a401280a@phx.gbl...
> how can I count all the occupied cells in the same column
> if the cells contain text and numbers?
>           A
>  row 1   br1
>  row 2
>  row 3
>  row 4   cb2
>  row 5
>  row 6
>  row 7   df6
>  ect down to row 200.
>
>  the answer should be 3 (the number of cells occupied)
>
>
> rgs  jerie
>

```
 0
Trevor9259 (673)
3/5/2005 7:34:21 AM
```Jerie,

=countif(a1:a200,"") will return the total number of blanks

thanks......jaya

"jerie" wrote:

> how can I count all the occupied cells in the same column
> if the cells contain text and numbers?
>            A
>   row 1   br1
>   row 2
>   row 3
>   row 4   cb2
>   row 5
>   row 6
>   row 7   df6
>   ect down to row 200.
>
>   the answer should be 3 (the number of cells occupied)
>
>
>  rgs  jerie
>
>
```
 0
Jaya (8)
3/5/2005 7:39:01 AM
```Hi!

Will there be any enties that are only numbers? Is there
always only a single digit? Are the digits always the last
character? Will there be any entries that are all letters?

row 1   br1
row 2   101
row 3   k22
row 4   cb2
row 5   44s
row 6   xxx
row 7   df6

Biff

>-----Original Message-----
>how can I count all the occupied cells in the same column
>if the cells contain text and numbers?
>           A
>  row 1   br1
>  row 2
>  row 3
>  row 4   cb2
>  row 5
>  row 6
>  row 7   df6
>  ect down to row 200.
>
>  the answer should be 3 (the number of cells occupied)
>
>
> rgs  jerie
>
>.
>
```
 0
biffinpitt (3172)
3/5/2005 7:41:44 AM
```jerie

=countif(a1:a200,">""") will return the number of cells containing text
=countif(a1:a200,">0") will return the number of cells containing +numeric

if you combine these two criteria with +, will return both text and +numeric

thanks....jaya

"jerie" wrote:

> how can I count all the occupied cells in the same column
> if the cells contain text and numbers?
>            A
>   row 1   br1
>   row 2
>   row 3
>   row 4   cb2
>   row 5
>   row 6
>   row 7   df6
>   ect down to row 200.
>
>   the answer should be 3 (the number of cells occupied)
>
>
>  rgs  jerie
>
>
```
 0
Jaya (8)
3/5/2005 8:43:01 AM
```  =COUNTA(A1:A10)

will return the count of cells with text or numbers, and also cells containing
error values and cells containing a formula that is returning a result of "".

On Sat, 5 Mar 2005 00:43:01 -0800, "Jaya" <Jaya@discussions.microsoft.com>
wrote:

>jerie
>
>=countif(a1:a200,">""") will return the number of cells containing text
>=countif(a1:a200,">0") will return the number of cells containing +numeric
>
>if you combine these two criteria with +, will return both text and +numeric
>
>thanks....jaya
>
>"jerie" wrote:
>
>> how can I count all the occupied cells in the same column
>> if the cells contain text and numbers?
>>            A
>>   row 1   br1
>>   row 2
>>   row 3
>>   row 4   cb2
>>   row 5
>>   row 6
>>   row 7   df6
>>   ect down to row 200.
>>
>>   the answer should be 3 (the number of cells occupied)
>>
>>
>>  rgs  jerie
>>
>>

```
 0
anonymous (74722)
3/5/2005 7:01:09 PM

Similar Artilces:

blank cells being counted ??
Wondering if someone could help with this problem After doing a vlookup and then cleaning up with pastespecial/values I am finding that the visually empty cells are still being counted in my counta totals I have tried this subroutine to clearcontents on the empty cells, which works if I do it manually. Sub clearempty() For i = 3 To 588 If Cells(i, 14) = "" Then ActiveCell.clearcontents End If Next i End Sub Can anyone tell me 1. why my clearcontents subrouting is not clearing and 2. is there another way to clear out this invisible data i...

Two sums on same field in same query based on second field criteri
I have a table called 07DATA which has two fields in it: DOLLARSPAID and DIRECTION. Dollarspaid is clearly a currency field which has multiple dollar values. Direction is a field which contains either an "I" or an "O". What I'm trying to do is to create a query with two fields that shows the total dollars paid for both "O" and "I". I can easily do this by creating separate queries and then a third query that returns the results but there must be a way to do this in just one query? Thanks in advance! SELECT IIf([DIRECTION] = "I"...

Count cells
Why is that my answer to my formula will not update? It was working fine at first not it do not. I want to count text in each cell (eg. A5-A9). The formula I use is =COUNTA(A5:A9). My answer will be 5. When I delete a row the answer do not update. Why? Press F9 to do a manual recalculation. Did that work? Then you worksheet is set to manual recal Open Options (what version of Excel are you using?) and reset to Automatic. best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "Danni" <Danni@discussions.microsoft.com> wrote in messa...

email message count
what's your question? If you're trying to get a count, use a third party product such as MessageStats (or use perfmon for basic counters). http://quest.com/messagestats D. "Marilyn" <anonymous@discussions.microsoft.com> wrote in message news:DF6A0DA4-BB2C-493D-B7A8-A96FAA583FFF@microsoft.com... > ...

use Vlookup for sum all found value?
Hi, I am new here and have a problem hope I can get help in here! I have two worksheet "W1" and "W2" In W1 worksheet, it has a column "emp_pin" which is unique. This value is used in the Vlookup function to find a numeric value "Paid" in W2 worksheet. If "W1" and "W2" are one to one relationship, it will work fine. However I have encountered the one to many relationship, it means one row in "W1" has many rows in "W2". I just wonder if I can sum up all the found value "paid" in "W2". should...

SUM Function not Updating
Hello, One of our business partners is having trouble w/an Excel document. They have the SUM function used multiple times throughout the document, and as they have changed values in certain fields, the SUM has automatically updated itself to reflect this. It's not working anymore. I have tried to redo the formulas but they simply won't work. The SUM stays the same regardless of the values in the cells. I can't even create a brand new column w/the SUM function at the bottom...it won't up date either. In a new document I can get the SUM function to work properly, how...

Our store has a number of different items that are tracked as kits... such as baked up meals, sandwiches, etc. Since there is no way to tell exactly how many sandwiches we can make to put in a starting number we have been starting at zero and going into a negative number for tracking purposes. Is there a way to set certain items in RMS to count up rather than down? Thanks for your help. ...

Counting Cells with odd and/or even values
Lets say I have range A1:A5 containing values 1,2,3,4,5 respectively. I would like to count how many of these cells are odd and place that value in A6. The same for even numbers as well (in A7). Any help would be appreciated. -- yungexec ------------------------------------------------------------------------ yungexec's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=22593 View this thread: http://www.excelforum.com/showthread.php?threadid=564950 Hi One way =SUMPRODUCT(--(MOD(A1:A5,2)=1)) For even numbers, change the 1 in the formula to 0 -- Regards Ro...

using count in a query
I have a query that I am using to show all records in a table, based on a couple criteria. One of the fields in the query [MEMBER ID] has data the repeats. I want to leave everything the way it is and add to this query a count of how many times a value in this field shows up in the table. I am able to do this if I only put [MEMBER ID] in a query twice, make it a totals query, and leave one on group by and put the other on count. When I try to just add [MEMBER ID] to my original query a second time and change one of them to count in the same way, the value for the count field will on...

Counting the greatest number
I want to build a query with which to find the greatest number of figures in the field.I have a field called Plates where single numbers are entered.For example Visits Plates John 3 Jim 3 Deuce 3 Liza 3 Lenny 2 Lorry 6 Obvisouly in the above example we can count that the number 3 is the biggest number. How can i express that in a query ? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200708/1 SELECT Your_Table.Plates, Count(Your_Table.Plates) AS CountOfPlates FROM Your_Table GROU...

set up a worksheet in excel like a check register with auto sum
I would like to set up a worksheet in Excel for my check registor that will automatically give me the balance. jthhousing, have a look here and see if this will do what you want, http://office.microsoft.com/en-us/templates/TC010186511033.aspx If not I have one set up, if you would like to have a look at it let me know and I will send you a copy -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "jthhousing" <jthhousing@d...

Sum of the Largest Values
Hi. I have a gradebook. I have 16 assignments, and thus have 16 columns for scores. However, I would like to include ONLY the highest 10 scores in the final grade. In other words, how can I find the sum of the largest 10 values in a 16-cell range? Thanks! =SUM(LARGE(\$A\$1:\$P\$1,ROW(INDIRECT("1:10")))) arary entered using CTRL+SHIFT+ENTER -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 00/02/03 ---------------------------------------------------------------------------- Best wishes to all, and hope f...

Is there a way to sum a column using criteria from multiple column
I'm not sure how to ask this, I am currently working on a spreadsheet that has an itemized worksheet depicting each budget approval (Sheet2). I have it tied into our complete budget so that it automatically adds and/or subtracts the amounts according to the date... =SUMIF('Sheet2'!\$K:\$K,"="&(TEXT(D\$5,"mmm-yy")),'Sheet2'!\$E:\$E) ....but I also need it to be specific to the "BUD#" and "LINE#" (see below). SHEET2 A B C D E BUD # LINE # CAF Date Constructed Key Field Dollar Amount AUBUD 01 12/9/05 =TEXT(C1,"mmm-yy"...

Shortcut key for the Auto Sum, Max, Min, etc. window
I am trying to find the shortcut key(s) that lets me change the setting/functionality of the Auto Sum, Max, Min, etc. window in MS Excel. The window I am referring to is located in the bottom right hand corner of Excel, i.e. two places to the left of the NUM (displayed when Num Lock is on) window. Thank you for your help. James, I think you may be referring to the AutoCalculate feature which appears on the status bar when you have selected cells with values in them. By default it gives the sum of the cell values, but you can right click on the calculation in the status bar to change...

Do Not count blank cells
Hi all!! I have been trying to figure out how to NOT have excel count blank cells when it is counting my information that my formulas are telling it to count. This is my current formula (array): =SUMPRODUCT(--(oc!\$C\$1:\$C\$1000=435),--(oc!\$H\$1:\$H\$2000="approved"),--(oc!\$D\$1:\$D\$1000="telemedicine") I have to put a space in each blank cell (which could be a bunch) Thanks Dee -- imjustme ------------------------------------------------------------------------ imjustme's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26854 View this thread:...

Sum
Hi, I have a following data in a column on my spreadsheet 0.00 -98.35 #N/A -222.89 56.45 0.00 13.60 19.96 When trying to use the Sum function it doesn't work as it doesn' appear to like the #N/A. Any suggestion would be gratefully received. Thanks Simo -- sgrec ----------------------------------------------------------------------- sgrech's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1450 View this thread: http://www.excelforum.com/showthread.php?threadid=27181 sgrech wrote: > Hi, > > I have a following data in a column on my spreadshee...

Columm sums
Hi all Thanks for help in the past & future I have a columm of numbers to be added There are about 1200 of these running say from cells C1 down to C1200 I normally use the =SUM(C1:C1200) function but often I only want to total part of these - say from cells 250 to 650 I usually manually change the function each time - I am wondering if there is some function I could use where I could input say into cells B1 and B2 the numbers of the range of cells in the C columm I want to be totalled Many Thanks Alex Hi Alex, If cell A1 contains the row no to start from - say 6 and cell A2 has...

How do I subtract a date from a date for a sum of total years?
I am trying to calculate how many years an employee has worked. I have start date and current date. What is the formula to return years worked? If the current date is in A1 and the start date is in B1, then =year(A1)-year(B1) formatted as general will return the number of years worked. -- Gary''s Student "Paige" wrote: > I am trying to calculate how many years an employee has worked. I have start > date and current date. What is the formula to return years worked? Hi Paige One way =DATEDIF(Startdate,currentdate,"y") for years only =DATEDIF(Start...

How to change the Subtotal of my Pivot table to use subtraction instead of Summing
I have the following Pivot table created: --------------------------------------------------------------------------- Sum of HEAD_COUNT DEPT PER_GROUP TYPE HQ HRD ED PD CFD --------- ---- -- --- -- -- --- ACCOUNTING 1APPROVED 0 0 0 0 0 0 2ACTUAL 0 0 0 0 0 0 ACCOUNTING Total 0 0 0 0 0 0 ADMINISTRATIVE 1APPROVED 0 1 0 0 0 0 2ACTUAL 0 0 0 0 0 0 ADMINISTRATIVE Total 0 1 0 0 0 0 CORPORATE SUP 1APPROVED 1 0 37 1 8 7 2ACTUAL 1 0 35 1 8 7 CORPORATE SUP Total 2 0 72 2 16 14 ----------------------...

Subtotal
OK, so I have a list of orders. Some orders have just one row of data, some have two rows of data, some have three, some have four, etc. Each row represents either a delay or an activity. (So, obviously, an order can have more than one of either.) I need to aggregate into one row: Order | Type of Order | Num. of Delays | Total Length of Delays HERE'S THE MANUAL WAY I'M DOING THIS NOW: ====================================== My initial columns are: Order Number (A) | Type of Order (B) | Action Code (C) | Action Length (D) I added a "marker column" - Dela...

Counting in Excel #2
I know nearly nothing about Excel syntax, so please be kind. I have a list of people who are eating at a seminar. So far I hav some blank cells, some N for no, some Y for yes. I want to count the cells to get the number of people eating. I have =count(b5:b100,Y) which does not work. I have the format a general if that helps any. Please don't laugh at my syntax. :) Thanks for your help -- Message posted from http://www.ExcelForum.com Hi try =COUNTIF(B5:B100,"Y") -- Regards Frank Kabel Frankfurt, Germany > I know nearly nothing about Excel syntax, so please be ki...

Count cells in range
I need a function that simply counts the number of cells in a range. Not based on a condtion, simply the number of cells in a range. Obviously if I'm doing this once I can simply put in a constant. (It's easy to see that the range A1:A50 has 50 cells.) But I need a formula that will automatically adjust if the size of the range changes, so that if I insert a row after A17, the formula will return 51. As far as I can tell, COUNT, COUNTA, and COUNTIF don't do this. I suppose I could use COUNTIF(A1:A50,"<>999"), where 999 is a value that I know will not app...

Count using a criteria
I have a spreadsheet in which I need to tabulate a total count of rows which meet a criteria. Example: Type Phase SubPhase "Occupied" 1 A I need a formula that counts the rows that meet a criteria such as Where phase is 1 and Subphase is A, count the number of rows that are "Occupied" Can anyone help - this seems like it should be easier. I may be missing something Thanks One way =SUMPRODUCT(--(A2:A100="Occupied"),--(B2:B100=1),--(C2:C100="A")) -- Regards, Peo Sjoblom "Dan Colgan" <anonymous@discussions.m...

How to count cells
Hi, I want to put a summary of Job orders in a table. (Excel (Zip) file attached) I am having a problem of total. I want to make a total of only approved job orders not the all job orders. Also if the column contains hot / cold type, I need red and bold for hot job order of complete row. Also I want to be counted by job type and entity. For example, in the total approved 7 permits, CONST entity 3 cold jobs and 1 hot job. how can i display in the abstract table. Can anyone help me please. +-------------------------------------------------------------------+ |Filename: count.zip ...

Count Nonblank Text Cells
Hello. I searched for a solution to this, but couldn't quite find what I needed. I am importing data into five columns in Excel. Each of the cells is formatted as text, but most of them are blank. I want a count of the ones that are not blank. I have five columns for problem codes, so each row will have between zero and five problem codes. This is a little trickier than I thought because the problem codes can be either numbers or letters or a combination of both. How do I write a formula that says, "Look in these five cells and tell me how many of them have something meaningful...