Count if different formula

I need a formula to count the number of different cells in a column.

Example
A1 = 25A99
A2 = 45C22
A3 = 25A99
A4 = 3195A
A5 = 3195A
A6 = 8621E

The total would equal 4.Four different values in the columns.
T.I.A. 0 9/11/2003 4:47:44 PM excel.misc  78881 articles. 5 followers. 2 Replies 283 Views Similar Articles

[PageSpeed] 44

http://tinyurl.com/n0s1

"dayton" <dayton500@hotmail.com> wrote in message
news:bjq8nu\$locs5\$1@ID-201461.news.uni-berlin.de...
> I need a formula to count the number of different cells in a column.
>
> Example
> A1 = 25A99
> A2 = 45C22
> A3 = 25A99
> A4 = 3195A
> A5 = 3195A
> A6 = 8621E
>
> The total would equal 4.Four different values in the columns.
>  T.I.A.
>
>
>
>
> 0  Don
9/11/2003 4:51:30 PM
One way:

Using your example, enter this formual in B1 and copy down to B6:

=COUNTIF(\$A\$1:A1,A1)

This will result in

A                 B
1  25A99              1
2  45C22              1
3  25A99              2
4  3195A              1
5  3195A              2
6  8621E              1

You can then use this formula to get the result you're after:

=SUMIF(B1:B6,1)

-Dave

"dayton" <dayton500@hotmail.com> wrote in message
news:bjq8nu\$locs5\$1@ID-201461.news.uni-berlin.de...
> I need a formula to count the number of different cells in a column.
>
> Example
> A1 = 25A99
> A2 = 45C22
> A3 = 25A99
> A4 = 3195A
> A5 = 3195A
> A6 = 8621E
>
> The total would equal 4.Four different values in the columns.
>  T.I.A.
>
>
>
>
> 0 9/12/2003 12:49:22 AM Similar Artilces:

Business Management Error Move CRM 4.0 Database to Different Machi
Hi, I have backed up a CRM 4.0 _MSCRM and MSCRM_Config database from one machine and restored both on another machine ( Which had been useing a test database). After restoring both databases on the new host I used the Deployment Manger to import the restored database. After completing the import when I attempt to open CRM I get an error message saying " Business Management Error The user ID associated the current record is not valid" From the server where I'm logged an as Administrator On client PC I get the following. Server Error in '/...

Excel: Replace strings with a Matrix formula
Hello NG, the following sheet: - In column A are words in German. - In column B are the translated words in English. - In C1 is one English clause with only one single German word in it. ToDo: I want translate this word in English and write it o D1. But I dont want to use VBA or more then one cell for the calculation. Such solutions I can make for myself. I'm working since several years with Excel and VBA. Currently I'm playing with this matrix formula. (I have translated the Excel function names from German to English, but I dont know if I used the right words.) {=IF(ISERROR(SEARCH...

MATCH formula #2
I have a spreadsheet that lists all the types of machines and thei model numbers. When a customer places an order for a specific machine I need to reference the other workbook in order to get the model number Is there a way to have this done automatically. For example: when enter a machine type (CE-10), I want the model number for that machin to be entered automatically in another cell, without having to open th other workbook and copy and paste the number myself. I considered dat validation (using a list) but can't reference another workbook. Any thoughts? Thanks, De -- da ------...

Formula to Calculate Dates
I need to enter one date, and have it compute the future date based on the number of years: For Example: - Date Added: June 2007 - Number Of Years: 5 Year - Expiration Date (Need Formula): June 2012 How would I get that result. BTW: Using Excel 2000 =DATE(YEAR(A1)+5,MONTH(A1),DAY(A1)) -- Kind regards, Niek Otten Microsoft MVP - Excel "S" <S@discussions.microsoft.com> wrote in message news:8504259F-4193-4CE3-9387-EAB68624076E@microsoft.com... |I need to enter one date, and have it compute the future date based on the | number of years: | | For Example: | - Date Added: J...

VLOOKUP formula searching multiple worksheets ??
Does anyone know how to search for info from multiple Excel worksheets using the VLOOOKUP formula or any other formula. I have my VLOOKUP formula(column B) and list of values(column A) (sorted in ascending order) on Worksheet 7 and I need to search for these values in Worksheets 1,2,3,4,5& 6 Column F. Is this possible with Excel ? I've tried this by using the formula VLOOKUP(A2,Wrk1:Wrk6!\$F:\$G,1,FALSE) but I get a #VALUE! error. You will need a construct along these lines: =IF(ISNA(VLOOKUP(A2,Wrk1!\$F:\$G,2,0)), IF(ISNA(VLOOKUP(A2,Wrk2!\$F:\$G,2,0)), IF(ISNA(VLOOKUP(A2,Wrk3!\$F:\$G,2...

Array formula
{=SUM((F45=10)*(H45={1,2,3})*{20,10,5})+((F45=20)*(H45={1, 2,3})*{42,21,11})} This formula works fine, up until the separated part, then it just does nothing(I put the spaces in just to clarify the problem area for this discussion). No matter what order put the arguments in, it only works up to the same point. Is this just a case of to much info? How do I extend this formula (I need it to be even longer the above example). thanks. Hi Atom, Try replacing SUM by SUMPRODUCT and do a normal completion with just ENTER since it is not an array formula Bernard "atom" <hmm@hmm.com...

This is a multi-part message in MIME format. ------=_NextPart_000_0006_01C61A25.29EC1E10 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I didn't receive a response on my last post so I worked on my problem = some and came up with some results. Could someone look over my formulas = and tell me if I can achieve this same result any easier? Here is a sample of the worksheet: D E F G H I J K L M N O=20 22 Sun Mon Tue Wed Thu Fri Sat Reg OT x1.5 OTx 2 Rate Total=20 23 off 12 11 11 11 11 off 40 15 1 10.00 \$645.00=20 ...

Can I use the NOW() function in a formula?
I have a cell B1 that has NOW() time and another cell B2 that has NOW() date. I am trying to have data from another cell F1 brought in to the destination cell B3, when a given date and time occur. =(IF(AND(B1="23:00:00",B2="12/03/2009"),F1," ") This formulas does not work. The Clock is continuously active / always changing. Any suggestions would be appreciated. Thanks. The NOW() function returns both a date and a time. Even if you format the cell to display only date/time, all the data is still retained. NOte that if you did want just the d...

Same CR report with different databasename
I had create a lot of report in myTesting server. the SQL server named 'SQL-001' however, my client's SQL server is another name , now, I can't preview the report after publish the web.app to it server, Any simple method to solve it ? I don't want to re-write all the report under their server. Thanks a lot Hi Sze, Based on my understanding, you are facing the problem to get the data from the client SQL Server after you publish the report to the server. If I have misunderstood you, please feel free to let me know. A simple way to solve this issue is...

Copy and Paste without copying Formulas
I have created a workbook with several sheets. The first sheet consists of a master list of 8000 numbers. The second sheet is where I paste a smaller list of numbers. The workbook performs a search and returns numbers that match on the third sheet. The third sheet also has formulas. I want to be able to copy and paste the resulting info on the third sheet to another workbook without copying any of the formulas......I just want the info. Any suggestions?? Ken Copy>Paste Special>Values>OK>Esc. Gord Dibben Excel MVP On Thu, 18 Nov 2004 14:21:13 -0800, "Ken"...

Results of formula vs real numbers
HI. How do you change a formula to read the value as a result of a formula and not necessarily as a real number? Example: In G6, i have the following formula that gives me the hour of a time in F6 =IF(F6>0,MOD(F6-"1:00",1),"") F6 contains 00:30:08, The result is 23 In another cell, I am using this formula, but it doesnt recognize the 23. =SUMPRODUCT(--(B6:B58331="james"),--(G6:G58331="23")) The 23 is actually the HOUR of time so it isn't a whole number or real number persay. It is a rounded time to the hour. Yes, but it isn't a text ...

Does batch recovery result in different transaction source values?
We have a situation that we can't explain. Here are the facts: 1. User posted a Receivings transaction batch comprised of 37 different receipt transactions. 2. In the G/L, transactions #1-32 have a ORTRXSRC value of 'RECVG00001854', a TRXSORCE of 'GLTRX00037046' and an ORGNTSRC of 'RECVG00001854'. 3. In the G/L, transactions #33-37 have an ORTRXSRC value of 'GLTRX037046', a TRXSORCE of 'GLTRX00037047', and an ORGNTSRC of 'GLTRX037046'. Note the slight difference (one less zero) in the TRXSORCE and ORGNTSRC values compared to transacti...

can i create formula giving totals based on financial & text info
Am i able to create a formula that gives me monetray totals for expenditure on hotels, split into totals for 6 varying business sectors? ...

saving the same file in different places at a time
hi, i have an excel file in one system when i update this file, is it possible some range of cells in this file to be saved in the different system also how do i give a link to the other file? appreciate, if anyone could help Sounds like you want to paste a link so that the cell will update every time the other cell gets new info... To do this, say you want to link cell A1 to cell A7...click on cell A7 and click copy...Go to cell A1 and click paste...Then (if you have Excel 2003), click on Edit/Paste Special/Paste Link and you now have youe link...The "Paste Link" Button ...

Formulas as text or zero
I have a spreadsheet that when I try and enter in a formula all that shows is the formula (i.e. =SUM(A1 +B1). Or if I try to just have it reference a cell, all I get is zero. Anyone have any ideas please. Hi check that the cell is not formated as 'Text'. If it is, change the format to 'General' and re-enter your formula -- Regards Frank Kabel Frankfurt, Germany "Steven011" <Steven011@discussions.microsoft.com> schrieb im Newsbeitrag news:0DA82667-7CA3-47C4-BA3E-A8807F99908F@microsoft.com... > I have a spreadsheet that when I try and enter in a formula ...

Different charts for different salesmen
I have a spreadsheet which shows, in rows, the type of business brought in by different salesmen. It also has a column which has each salesman's initials in it. So the seller in each row can be identified. The relevant part of the spreadsheet looks like this Initials Date Product Initials Date Product Initials Date Product Initials Date Product I want to produce a chart of the business types sold by each salesman. Obviously I can do it for the whole firm but how do I produce a chart for each salesman? Thanks Rob Graham You need to prep the data. You could simply filter the dat...

Excel Display formula, not result
I'm modifying a formula and rather than the result, Excel is displaying the formula -- Yes, my equals sign is in place still. The formula I'm modifying is =SUBTOTAL(9,E2:E45) to =SUBTOTAL(3,E2:E45) I'd rather not re'run the subtotal again to get the count I need unless I can get it on the same line as the sum. When I run them separate, I am getting two groups when I only want one. SPenney ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ Your cell ma...

Difference in Tax Period Report and Tax Transactions Report
Hi Has anyone ever encounter a difference in Tax Period Report and Tax Transactions Report? Thanks in anticipation. Best regards Kedalene Chong Application Consultant (HelpDesk & Support) ...

How do I combine different worksheets into one book?
I have several worksheets using same template for excel, how do I form one workbook and import all these seperate sheets into it?? one way: open each workbook and right click on the sheet name you want. copy or move then select a new workbook and make a copy. HTH "Madcat" wrote: > I have several worksheets using same template for excel, how do I form one > workbook and import all these seperate sheets into it?? > ...

Help with this formula!
Morning all, =INDEX('[PPfV F A.xls]input plus'!\$D\$1755:\$D\$1798,MATCH(1,('[PPfV F A.xls]input plus'!\$DJ\$1755:\$DJ\$1798=E5)*('[PPfV F A.xls]input plus'!\$DG\$1755:\$DG\$1798=MIN('[PPfV F A.xls]input plus'!\$DG\$1755:\$DG\$1798)),0)) What im trying to achieve is for the formula to always pick the lowest and next lowest value from the range DG1755:DG1798 based on E5 always being a set value. example: E5 specifies a risk value (say 2). DG1755:DG1798 contains products with a product rating. ie the no1 product has a risk val of 2 ..........no2 product has a risk val of...

How can I copy a formula where the referenced cells have an assoc.
I want to copy a formula where the cells have an associated range name but all I get is a repeat of the original formula. I want to have formulas where the cell references change as the formula is copied. That is the purpose of Named Ranges. They do not change. They are not relative. Say Namedrange is A1 =Namedrange * B1 will change to =Namedrange * B2 if copied down column C, but Namedrange will remain the value in A1. On another note.......if your cellsrefs like B1 are not changing to B2, B3 etc. perhaps you have Calculation on Manual mode. Check Tools>Options>Calculation. ...

copy formulas by dragging vertically
I have Sheet 1 with raw data in it. Sheet 2, Cell A1 is looking to Sheet1, H1 for the data (ex. =Sheet1!H1). What formula can I use in cell Sheet2, B1 that will tell it to look to cell Sheet2, A1 and copy the cell just below Sheet1, H1 or (=Sheet1!H2)? Not exactly what you asked for, but will this work for you? In Sheet2, A1, Enter this formula, and drag right to copy as needed: =INDEX(Sheet1!\$H:\$H,COLUMNS(\$A:A)) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ==========================================...

Formula problem on network
Produced a workbook containing several worksheets and named ranges. A couple of columns containing formulas work OK on stand alone computer. However, now in use on a network of 20 or so computers the formulas in these columns will sometimes show #NAME? error message, even though on another terminal they work OK. I'm mystified and any help or leads would be much appreciated Thanks Philip Are they using functions from the Analysis Toolpak, and that is not installed on some machines? -- HTH Bob Phillips "Philip Reece-Heal" <philip.reece-heal@dial.pipex.com> wrot...

Display count of characters in a cell while typing.
Not sure if this should be here or in the worksheet.functions newsgroup but I think here is a safe spot. As a user types text into a cell I would like to have another cell display the number of characters that are currently in it. The count has to be 'live', meaning that as the person is typing the counter is updating, not simply updating after the user has pressed 'Enter'. Alternately, a count-down counter would be even nicer ;-) Suggestions? Search brought up nothing. -- Toby Erkson Oregon, USA Excel 2002 in Windows XP Hi not really possible as macros don&#...

Copy formula so destination displays formula as text
Cell C123 contains the value 99. I want cell A1 to display that value (99) and cell B1 to display the formula (=C123). I put =C123 in cell A1. What do I put in cell B1? I know I can toggle between value and formula but how do I get them to both display side-by-side at once? THANK YOU! Here is a UDF solution that will work: Function GetFormula(Cell) GetFormula = Cell.Formula End Function Then in B1 enter =GetFormula(C123) -- Regards, Dave "Omunene" wrote: > Cell C123 contains the value 99. > > I want cell A1 to display that value (99) and cell B1 to display...