#### Count number of instances

```Need a formaula  to look at an entire column and count the number of
different times (say date of product)

Thanks
--
ce
```
 0
Utf
6/4/2010 2:41:57 PM
excel.worksheet.functions 4936 articles. 0 followers.

4 Replies
926 Views

Similar Articles

[PageSpeed] 12

```Do you mean a unique count..=COUNT() will give you a total count of numeric
values (including date, time, numbers etc; ).

Why dont you post few samples...

"Curtis" wrote:

> Need a formaula  to look at an entire column and count the number of
> different times (say date of product)
>
> Thanks
> --
> ce
```
 0
Utf
6/4/2010 2:47:46 PM
```Yes sorry unique count.

If I have Products in column b

apples
apples
peach
grape
lemon

I want to count the number of unique products as in this case the result
would be 4
--
ce

"Jackpot" wrote:

> Do you mean a unique count..=COUNT() will give you a total count of numeric
> values (including date, time, numbers etc; ).
>
> Why dont you post few samples...
>
> "Curtis" wrote:
>
> > Need a formaula  to look at an entire column and count the number of
> > different times (say date of product)
> >
> > Thanks
> > --
> > ce
```
 0
Utf
6/4/2010 3:04:22 PM
```Try the below.

=SUMPRODUCT((B1:B20<>"")/COUNTIF(B1:B20,B1:B20&""))

"Curtis" wrote:

> Yes sorry unique count.
>
> If I have Products in column b
>
> apples
> apples
> peach
> grape
> lemon
>
> I want to count the number of unique products as in this case the result
> would be 4
> --
> ce
>
>
> "Jackpot" wrote:
>
> > Do you mean a unique count..=COUNT() will give you a total count of numeric
> > values (including date, time, numbers etc; ).
> >
> > Why dont you post few samples...
> >
> > "Curtis" wrote:
> >
> > > Need a formaula  to look at an entire column and count the number of
> > > different times (say date of product)
> > >
> > > Thanks
> > > --
> > > ce
```
 0
Utf
6/4/2010 3:12:12 PM
```If you wanted to count the number of unique entries in that range, you could use
a formula like:

=sumproduct((a1:a10<>"")/countif(a1:a10,a1:a10&""))
(you can only use the entire column in xl2007)

Curtis wrote:
>
> Need a formaula  to look at an entire column and count the number of
> different times (say date of product)
>
> Thanks
> --
> ce

--

Dave Peterson
```
 0
Dave
6/4/2010 3:15:49 PM

Similar Artilces:

floating counts
My spread sheet has several colums using COUNTA. For example: =COUNTA(A3:A105). Each week I might insert 30 or more rows that I want included in the count. Is there an expression in the COUNTA formula that I can use to "float" the formulas to a row just below the last row containing data so I do not have to modify the ending row number (A105 in this example) when new rows are inserted? Bob Assuming your formula will be in A106 to start and you always want to start with the 3rd row: =COUNTA(OFFSET(A106,-ROW()+3,0,ROW()-3,1)) "Bob in Granger" wrote: > My spr...

Counting question
Hello, Column 1 contains a product ID and could contain just one entry or several entries depending upon how many reels of that product ID we have in inventory. Column 2 has the reel quantity in feet. I need to get a total quantity in feet for each product ID. Column 1 Column 2 Prod ID Feet ABC 100 ABC 150 ABC 200 XYZ 50 XYZ 50 I need the total product, in this example ABC = 450 ft and XYZ = 100 ft. I have over 3000 lines and over 600 products I can't do it manually. I think a pivot table would do it but I'm not sure... tks, st...

Excel 2000 spreasheet starts at line number 2
Hello dear members of the microsoft.public.excel.misc newsgroup. Please I would need your kind help on this one here. I've got an Excel 2000 documents inluding many differents spreadsheets. One of those spreasheet present the following characteristic, the lines begins at number 2. Even if I do insert a new line the new one is the line number 2. How could I remediate this please. Thank-You Octavio First go to cell A1 by pressing F5, entering A1 and clicking OK. Then do a Format, Row, Unhide. -- Jim Rech Excel MVP It sounds like you might have Windows/Freeze Panes selected. Andy...

Different counts on same query
Good afternoon, I need help on finding out how to run three different counts on one query. I have a table set up to show Date Within this query I have the following fields: Date | Method | AgedDays ... taken from TotalInventoryTable my goal is to say the following in one query. Date Method CountofAge CountofAge>30 CountofAge>60 Date Calls ## ## ## Date Corr ## ## ## etc. etc. All on one query... Is this possible. Thank you everyone. -- Message posted via http://www.accessmonster.c...

I am a long time user of Outlook Express. I have recently intalled Office 2007 beta and moved over to Outlook using the conversion tools provided in 2007. When I now start up my system, at the point where I have to select which user I am on my machine (it is set up for use by myself and my wife) the display claims that there are Two Unread Messages. Once I open Outlook it shows no messages as Unread in the Inbox nor does it show any messages in the virtual folder Unread Messages. I have run an advanced search on the Personal Folders using the criteria of Unread and this comes back w...

Make all number values BOLD that meet criteria ?
New to the board ! I'm sure my question is easy for all you gurus ! I've read around and it looks like I will need to use VBA to do what want but I want to make sure. I am working with lotto numbers so you have five columns x about 150 rows , each cell with one number value . I would like to make BOLD only the prime numbers. THEN...I want to have excel count how many prime numbers in each row. suppose you could just have excel count the number of bold values afte you have excel turn all prime bold. Anyway, conditional formating gives me only 3 filters. There are 1 prime number...

Counting a count
How do I count a count?? This is what im trying to do: I have a database with a table named tblMain and has incorporated everything within that table but contains info from about 8 other tables. I have not doesigned this database and it appears to have a very poor structure...anyway There are three fields involved in this process...'Request' (either 'dispensation', 'waiver' or 'dispensation and waiver'), 'Department' (which consists of 7 other drop down menu options) and 'RequestStatus' which contains the status of each request by another...

count question
Hello Experts, I have a table with four columns and 3000 distinct records. One of the aforementioned columns is named case_number with many duplicates in that column. I created a query to count the number of distinct case_numbers within the column (something that I do all the time w/ SQL in ORACLE). E.g., select count(distinct(case_number)) from table_1; Access, however, returns a count of all case_numbers, including duplicates. I created this workaround: SELECT Count(case_number) AS COUNT FROM [select distinct case_number FROM table_1]. AS [table_1]; I'm wondering why Access won&...

Counting Records after filtering by feilds
I am working on a project that will create a report to count the number of open work orders (records that have their opendate field with in a date range given), close work orders (records that have their closeddate field with in the same date range given earlier) and pending work orders (records that do not have an entry in the closed date field). I don't know how to (a) prompt the user for a given date range, im thinking of maybe using a form interface? Is it possible for me to have a calander button to click to select a date from there? and (b) To count the what was previously describe...

Track Serial Number
Dear All, One our client has got something like 4000 Item SKU's in his Inventory Master. Currently he does not track Serial Number. Due to business requirement, he has decided to Track Serial number for all the Item in his Inventory Master. Is it possible to enable Serial Number tracking without having to create new items in IV master. Please suggest. Thanks in advance for your support. -- Thanks & Regards, Venkatesh M On Nov 11, 10:04=A0pm, VenkateshM <Venkate...@discussions.microsoft.com> wrote: > Dear All, > > One our client has got something like 4000 Item S...

Outlook Multiple Instances Appearing?
Hi, In the last few days, (possibly since I downloaded and installed the Office 2007 beta [NB I didn't install Outlook 2007 as the documentation warned that you can't have both versions at once], and I installed Office 2007 in addition to the 2003 version I am running, I even installed it to a different folder location to try and ensure as much of the files were kept separate), I have noticed that I am getting multiple instances of the Outlook application's main interface in the task bar, which never used to occur, I would always get one copy of the main page, and then diffe...

Inclusive number of days and running total
Hi, I have a column of start dates (B) and a column of end dates (C). I would like to have the inclusive total of days. I would then like to have a running total of days for the callendar year as well as the total for any year and a grand total. Have been playing with DATEDIF but no luck so far. Cheers, Chris Just subtract the date in C from the date in B and format as General. Then SUM as required. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Chris" <Chris@discussions.microsoft.c...

Counting rows, then counting values.
I have a lot of spreadsheets with many rows and columns of numbers. I need a way to find the highest value in each row, (mark it some how, bold the number maybe), then count the number of "highest" values are in each column. Is there an easy way to do this? Thank You, Michael Hi Michael You can use this to get the value =MAX(A1:A20) and this to count =COUNTIF(A1:A20,MAX(A1:A20)) -- Regards Ron de Bruin http://www.rondebruin.nl "Michael via OfficeKB.com" <forum@nospam.OfficeKB.com> wrote in message news:152907df2ac24b6ab6ee8d51d46958d9@OfficeKB.com... >...

International Phone Numbers #2
Just a suggestion, but I think that there should be some sort of option to set up a customer as domestic or international, and for the Customer/Vendor card to reflect this. What I mean is that when you have an international phone number, the phone number fields should change to be able to accept international numbers, with a space for the country code. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the butto...

Counting nodes
Hi guys, I know that counting of nodes is not a big deal. But I need to do something where I cant get any solution. Here is how my xml looks like. <?xml version="1.0" encoding="utf-8"?> <CategoryList> <Category> <MainCategory ID="3">XML</MainCategory> <SubCategory>Basic</SubCategory> <SubCategory>Advanced</SubCategory> <SubCategory>blabla</SubCategory> <Description>List of XML articles.</Description> <Active>Yes</Active> </Category> &...

Page Count
I thought Excel used to display the number of pages with data on the bottom status bar. I that option gone or is it only in Word? -- pk Excel has never, in my memory, given that information on the status bar. Perhaps you once had some code that gave you a page count. Gord Dibben MS Excel MVP On Wed, 11 Jan 2006 15:01:02 -0800, "pkferret" <pkferret@discussions.microsoft.com> wrote: >I thought Excel used to display the number of pages with data on the bottom >status bar. I that option gone or is it only in Word? ...

Timesheet and Expenses Next Document Number
I'm looking for a way to programmatically get the next document number for timesheets and expenses for use with Biztalk. Thank you Sounds tricky. Are you planning on selecting the next number ready for use or a number that is already used in PDK? Out-of-the-box PDK uses the application logic-layer to create the next number and to the best of my knowledge does not "store the next number" in any table. It derives the number by EmployeeID+Prefix Defined + Date + Running Number -Kevin "oughgr" wrote: > I'm looking for a way to programmatically get the next...

How does Access count characters?
When I copy text into Word and view the character count, the number is different than what I get with Len(Trim[fieldname]). How does Access count characters? Thanks. David Could you give us a hint? Are the counts off by just one or two, or by a lot? Do you have an example of something that Word counted and Access counted, but came up with different counts? More info, please... Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service...

Numbering in Office 2007
Can someone please tell me why, when I start a numbered list in Microsoft Office 2007. When I reach the number 100, 101, 102 etc. and then type the titles after each number, Microsoft Office automatically leaves a half inch gap between the number and words. I have tried everything to backspace over the gaps, no luck!!! I am running Windows XP Professional It is because you are not aligning the numbers. Try this: 99. Create the numbered list. 100. Select the list items you want to realign. 101. You'll find the Numbering tool in the Paragraph group on the Home tab. Cli...

Counting Hours in Multiple Days
I'm attempting to do something that is currently beyond my skill level. Here's the problem: I have the following columns: Start Date/Time, End Date/Time. I want to count the number of hours each day for each day of the period. I can get the total number of hours easily. However given: Start Date/Time End Date/Time 07/29/2007 15:00 07/31/2007 20:00 That's a total of 57 hours. What I am looking for is a way to break out each day: Day 1 = 13h Day 2 = 24h Day 3 = 20h Day 4, 5, 6 - Day 31 Ideas greatly appreciated. Alan So, you could potentially have 31 days difference b...

Count if criteria is part of list
Want to be able to count all value in particular range that have a value that appear in list of multiple values eg have spreadsheet all different types computers and what versions of o/s and service pack they are running eg Sheet 1 A B Windows XP Professional SP2 Dell Inc. Windows XP Professional SP2 Dell Inc. Windows 2000 Professional SP4 Compaq Windows 2000 Professional SP4 Dell Inc. Windows 2000 Professional SP4 Dell Inc. Windows 98 sr2 ...

count #2
Hi - I'm hoping someone can help me - i have a list of employees (col A) who have been awarded \$ amounts (col B). I need to count the number of times they have received an award... really would appreciate any help... thanks 00020084 1,500.00 00020084 1,000.00 00020084 500 00018539 1,000.00 00018539 1,500.00 00028267 1,000.00 =COUNTIF(A:A,"00020084") -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Lacey" <Lacey@discussions.microsoft.com> wrote in message news:BB9245B1-BD2B-48F2-B177-5F6D0851BB37@microsoft.com...

Get the Name and Phone Number of the Current Windows User from Outlook 2003
Get the Name and Phone Number of the Current Windows User from Outlook 2003 Hello All, I have asked the question yesterday here: http://groups.google.com/group/microsoft.public.dotnet.languages.csharp/browse_thread/thread/be5f6b36fdef1345/24abc34416f227e4 I got a lot of help there, but I may posted in the wrong place. I also realized that I am using Outlook 2003. The new outlook object model 2007 does not work. I may need to use API. Here is an updated description.: I am writing a simple .NET (C#) application. It needs to "automatic" get the Name (last, first) and phone num...

How do I count selected controls in a formula
I have a worksheet that is my log of occupational injuries and illnesses. Each row is a unique record for which information is recorded in the columns. Four of the columns request and "check mark" to select the right classification of injury. Because only one of these columns can have a check mark, I've placed option buttons from the Control Toolbox in them. Two questions: 1) At the bottom of each column is a totals line. How do I write a formula in the cell that counts the total number of option boxes that have been selected in the column? And doing so without kno...

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...