#### counting the number of occurences

```I have a list that shows the worker ID and the result of each work item.  The
number of work items differs for each worker ID--the results of each item is
either "A" or "D".  This is an example of a portion of the list:

Worker   Status
008Q         A
008Q         D
008Q         A
098Q         D
098Q         A
098Q         D

I am trying to set up a new list that counts for each worker ID the total
number of work items with status "A".  How can I go about doing this?

```
 0
Utf
12/16/2009 3:37:02 AM
excel.worksheet.functions 4936 articles. 2 followers.

3 Replies
1549 Views

Similar Articles

[PageSpeed] 33

```Assume your data is in the range A2:B7.

List the unique IDs in a range of cells:

D2 = 008Q
D3 = 098Q

Enter this formula in E2 and copy down as needed:

=SUMPRODUCT(--(A\$2:A\$7=D2),--(B\$2:B\$7="A"))

--
Biff
Microsoft Excel MVP

news:11D1E3C3-A0C6-42A1-8E8D-66AE49AB686E@microsoft.com...
>I have a list that shows the worker ID and the result of each work item.
>The
> number of work items differs for each worker ID--the results of each item
> is
> either "A" or "D".  This is an example of a portion of the list:
>
> Worker   Status
> 008Q         A
> 008Q         D
> 008Q         A
> 098Q         D
> 098Q         A
> 098Q         D
>
> I am trying to set up a new list that counts for each worker ID the total
> number of work items with status "A".  How can I go about doing this?
>
> Thanks.  Let me know if you need more information on this.
>

```
 0
T
12/16/2009 4:06:32 AM
```Hi,

Create a pivot table.  Drag worker to the row area, status to the column
area and column area (again) to the data area.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

news:11D1E3C3-A0C6-42A1-8E8D-66AE49AB686E@microsoft.com...
> I have a list that shows the worker ID and the result of each work item.
> The
> number of work items differs for each worker ID--the results of each item
> is
> either "A" or "D".  This is an example of a portion of the list:
>
> Worker   Status
> 008Q         A
> 008Q         D
> 008Q         A
> 098Q         D
> 098Q         A
> 098Q         D
>
> I am trying to set up a new list that counts for each worker ID the total
> number of work items with status "A".  How can I go about doing this?
>
> Thanks.  Let me know if you need more information on this.
>
```
 0
Ashish
12/19/2009 12:10:30 PM
```Hello Bradly,

I would use a Pivot table.

If you do not like that you can use my UDF Pstat, for example:
http://sulprobil.com/html/pstat.html

Regards,
Bernd
```
 0
Bernd
12/19/2009 2:14:04 PM

Similar Artilces:

Column Numbering
Ok, I'm sure this can be done and it might have already been posted but this is what I'm wanting to do. I have a spreadsheet of sports cards of a particular player that I collect. I'm wanting to have a column of number, 1,2,3,4,5,..... etc so I'll know how many cards of that particular player I have. Now here is my problem. Because my spreadsheet is alphabetized and also is done by year, starting with 1989 when the player was drafted, anytime I get a new card I insert a row into the spreadsheet to input the card information. So what I want it to do when I insert a new row...

Number of the day of the year
I need a formula that will look at a date and tell me what number day it is in that year. eg 31/1/2007 would show as 31. 1/3/2008 is 61. It is sort of like a julian date but without the year part of the number. Thanks Try the below with date in cell A1 =DATEDIF(DATE(YEAR(A1),1,0),A1,"d") -- Jacob (MVP - Excel) "NDBC" wrote: > I need a formula that will look at a date and tell me what number day it is > in that year. eg 31/1/2007 would show as 31. 1/3/2008 is 61. It is sort of > like a julian date but without the year part of the numbe...

Create seqiuence numbers?
I would like to create a column of sequence numbers that is identical to the sequence numbers excel puts on the rows. My goal is to preserve information about the order in which the rows were created. Is there a quick way to do this? Or must I type in all the numbers? TIA LAS You could use a formula like: =row() and copy down as far as you need. But if you delete/insert row(s), then these formulas will change accordingly. You may want to convert them to values (edit|copy followed by Edit|pastespecial values) On 06/23/2010 15:14, LAS wrote: > I would like to create a column of ...

how to include order number in econnect
I am generating an order schema that I want to import to Great Plains. I would like to use the tagetsopnumber stored procedure in my schema to get the document number for the sales order header and lines. However, I cannot figure out how to include the stored procedure into the econnect schema and how to retrieve the value of the next available doc number and assign it to the document number node of the schema. my email is jacktundra@hotmail.com if someone wants to email me an example. "Jack" wrote: > I am generating an order schema that I want to import to Great Plains. I...

Custom Number Formatting Troubles
I am trying to set a custom number format for some cells to automatically add a hyphen between every two sets of numbers. I am trying to be able to punch in the MAC address of a piece of hardware and have it automatically input the hyphens in the address for me. For example, an address like this: 0A-00-3E-F0-12-34 or like this 0A-00-3E-F0-1B-4C. ----- I have tried the following code: ##-##-##-##-##-## and it works fine until you enter in the alphabetic characters. So, I modified my code to this: 0\A-00-3\E-F0-##-## and it will work ok except in the case of an alphabetic character in the...

how do I automatically add incremental numbers to a cell
Hi! I'm a bit thin on information, but have a look at Edit > Fill > Series Al -- Alf ----------------------------------------------------------------------- AlfD's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=478 View this thread: http://www.excelforum.com/showthread.php?threadid=26940 ...

Counting Largest repeating value in a field
I have a field in a table that uses a defined 2-place number format (such as 28 or 53). Can anyone help with the coding to find which 2- place number is used the most frequently, and how often it's used? For instance, if the field has 28 in 1000 records, and 53 in 2500 records, the coding should return 53 and 2500. FYI - I intend to use this in a report. Thanks in advance. <Scott.McCoid@fedex.com> wrote in message news:073198cf-0021-4070-b99c-5518b0ac150d@n20g2000hsh.googlegroups.com... >I have a field in a table that uses a defined 2-place number format > (such as 28 or 5...

Rounding numbers in charts
HHi U'm not very good with computers. And I did a spreadsheet on excel and had percentages I wanted to put in a chart...when I created the chart (piechart) the values came out all inacurate. I wanted one to be 8% and it came out 6%. I heard you couold try and fix it using decimal places but that only makes a minor difference. Is there something I dont know about thats fairly simple to do? Hi Andrea If you'ld like to give us an example of your data (e.g. three categories and the associated percentages) and explain to us where the percentages came from - did you type th...

number style to merge to Word
Using Office 2003. I have an Excel data table that includes a column of dollar amounts. Some of these amounts are four or five digits and are formatted in Excel to use the comma separator for thousands. When I merge this Excel data into a form letter in Word, the comma separator does not carry forward into the Word document, so now the four and five digit numbers become difficult to read. I don't want to have to manually go back into every merged document (too many!!!) to manually add the comma as the thousands separator. Any suggestions??? (Saved from a previous post.) Deb...

When printing 4 tickets per page, how do I number them?
I am printing tickets, 4 to an A4 page, and would like to number them automatically using the insert page number function. Is this possible, is there another way??? Mail merge is the only way if you want consecutive numbers. Setup a database. When you merge, you can only have one ticket on your screen. Mail merge http://office.microsoft.com/en-gb/assistance/CH010504371033.aspx -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Trundle" <Trundle@discussions.microsoft.com> wrote in message news:C4A2FF17-DFEB-4997-A98...

Counting multiple field values in a single query/report
I am creating a database for a community program that includes a number of fields with drop down choices, such a race, school, abuse history, family composition. We want to total/count each of these variables for a particular date range. I can create separate summary queries to get totals for each, such a total number of african americans, total white, total asian, total other, but then the date range has to be re-entered over and over. Have tried creating one master query/report for just the date range, then pulling in the others as subreports, but it ends up showing each re...

Excel 2003: numbers copied from website are treated as text
I copied several columns of data (Verizon wireless detail data), and one column (minutes) is treated as text. I want to use the values in a calculation (summation), but that gives a data type error. The Help on converting numbers stored as text advises: 1. On the menu: Tools | Options | Error Checking 2. Make sure "Enable background error checking" and "Number stored as text" both Selected, click OK 3.On the worksheet, select any single cell or range of adjacent cells that has an error indicator in the upper left-hand corner *** no cells have an error indicator **...

How do I make the smallest number in a column red ( No VBA Please )
for a example in column E at cell E10 to E 30 I want the smallest number I enter in each of those cells to be red. If 3 cells are "0" all 3 should be red if one cell is "2" and all other cells are more than 2 I want that "2" cell to show up as red font. To sum it up in cells E10 to E30 I want the lowest numbers to be red if there are more than one of the lowest all should be red Thanks Ron On Sun, 5 Dec 2010 13:47:01 -0800 (PST), e_a_g_l_e_p_i <eaglepi@hotmail.com> wrote: >for a example in column E at cell E10 to E 30 I want the smallest >number I...

My Excel column headers have changed to numbers, how do I get bac.
For no reason whatsoeverwhen I open Excel, or an Excel worksheet the column headers are numeric instead of alpha. I have no idea how to get back to alpha. I have done a virus check. Help please Go to Tools>Options>General & remove the check for R1C1 HTH |:>) "Monty" wrote: > For no reason whatsoeverwhen I open Excel, or an Excel worksheet the column > headers are numeric instead of alpha. I have no idea how to get back to > alpha. I have done a virus check. > > Help please Thanks so much - have a good weekend. Hot as hell in sunny SA. &qu...

Macro to add numbers to create combinations that equal certain amo
Thank you for your assistance. I receive a list of numbers, usually over one hundred items. I need to find out which amounts will add up to a given total. How would I create a macro to do this? Is there already an Excel function to do this that I have not found? It would need to work with currency. Ex: 9, 12, 7, 4, 15, 8, 10, 3 total needed 18 Solution 8+10; 3+15 Lauren, With a hundred different items, there are over 2^100 possible ways to combine them. You would need a supercomputer - or maybe even multiple supercomputers. If you had fewer than 25, you could do it. HTH, Bernie...

COUNT items in a list
When I'm working I make use of the quick totals feature in the statu bar at the bottom of the excel screen. Most of the time, I use the SUM setting, but sometimes I use the COUN setting if I want to do a quick count up of items. What I have tried to do for a long time is achieve the same thing usin one of the COUNT functions. You can countif or countblanks but as fa as I can see you can't countitems in a range. I have Excel 2000. Can someone advise me of a way to achieve this please -- Message posted from http://www.ExcelForum.com Counting a list of alphanumeric items requires ...

Sorting Columns by number of characters
I'm using Excel 2007. Is it possible to sort a column by the number of characters on the line? I track part numbers - which vary in length and can include letters and hyphens. Using just sort (smallest to largest) sorts by the beginning numbers. Being able to do a sort like this could cut down the time it takes me to do the next step in my tracking. Thanks! Nicole Just add a column with the formula: =len(a1) then sort on that. Regards, Fred "NicoleS" <NicoleS@discussions.microsoft.com> wrote in message news:9A0033AD-701A-4023-BA5A-1DF2025400A...

count rows of recordset
Hi Is it possible to count the rows of a RecordSet without stepping throug it? greez masterphil Yes use the recordset propperty's: .. movelast ..recordcount to find the number of records. (instead of stepping you've jumped :) ) -- Dnereb ------------------------------------------------------------------------ Dnereb's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26182 View this thread: http://www.excelforum.com/showthread.php?threadid=478456 =count(a1:a20) will count numbers =counta(a1:a20) will count all values Or you can try the Subtotals fe...

Converting numbers into words?
We would like to be able to convert 125.00 to one hundred and twenty five. Search Google for: Converting numbers into words in Excel You'll get a raft of solutions, the first being: http://www.ozgrid.com/VBA/ValueToWords.htm The next appears to be the same solution direct from Microsoft: http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q213360 Regards Trevor "Dean" <Dean@discussions.microsoft.com> wrote in message news:BB50451B-B2E7-4933-A1E1-6D172F88C30E@microsoft.com... > We would like to be able to convert 125.00 to one hundred and twenty five. ...

Auto Numbering On Printing.
I was recently given some fantastic advice on an auto numbering system while using Word. I would like to do this in Publisher as well. I have a single page that needs to to printed up to 150 times. I would like "Voucher No. 0001, Voucher No. 0002 etc. on each print. As I cannot insert a bookmark in Publisher, I cannot use the macro (http://word.mvps.org/FAQs/MacrosVBA/NumberCopiesOf1Doc.htm) Any help would be appreciated. Thanking you in anticipation Big Rick A tutorial by Brian - -Consecutive Numbering http://www.publishermvps.com/Default.aspx?tabid=95 -- Mary Sauer MSFT MVP htt...

Increment cell number when printing
I would like a particular cell in my spreadsheet to be incremented b the value of one after I have printed the worksheet. Any ideas on ho I can achieve this? Thanks Goody -- Message posted from http://www.ExcelForum.com Hi the following increments the cell before printing (put it in your workbook module - not in a standard module). It will increment cell A1 on sheet1: Private Sub Workbook_BeforePrint(Cancel As Boolean) worksheets("Sheet1").Range("A1").value = _ worksheets("Sheet1").Range("A1").value + 1 End Sub -- Regards Frank Kabel Fran...

Need to Change Column of Telephone numbers to just 10 characters
Hi I imported data from another program into excel. The telephone numbers are in the following format 333 333-3333. How do I change it to just having 10 digits without spaces of hyhens. I need it in this format to export to another system Jen Edit>replace find what -, leave replace with blank, then replace again and this time put a space in the find what and replace with nothing -- Regards, Peo Sjoblom "Jennifer Leen" <anonymous@discussions.microsoft.com> wrote in message news:7923E5C4-B199-464F-B34D-9B3B2C726376@microsoft.com... > Hi , > > I imported d...

convert numbers in Pivot table to Symbols
I am working on a compliance scorecard using Excel to measure if student homework are submitted on time. I get results in pivot table from a SQL db, the basic results in excel are: for On-time, I get number '1' for Missing, I get number '0' for Late, I get number '3' How can I convert these numbers to a reader friendly symbol, say convert 1 to a green 'check mark', 0 to a red 'X' and convert 3 to a 'black flag' ? Thanks !!! -- maxlog ------------------------------------------------------------------------ maxlog's Profile: http://www...

Generating random numbers from a given range
I want to generate 4 random numbers from a range of 1 through to 24. Each number has to be different from the other 3 though. Is there a way to do this? How about: Sub pickum() v1 = randbetween(1, 6) v1 = v1 & Chr(10) & randbetween(7, 12) v1 = v1 & Chr(10) & randbetween(13, 18) v1 = v1 & Chr(10) & randbetween(19, 24) MsgBox v1 End Sub -- Gary''s Student - gsnu201001 "Darren" wrote: > I want to generate 4 random numbers from a range of 1 through to 24. Each > number has to be different from the other 3 though. Is the...

calculate percentage of numbers <= zero in a given range
I need to calculate the percentage of numbers, within a range of cells, that are equal to zero or less than zero. Simple example: numbers are 8, -1, 8, 8, 0, 0 The percentage of numbers that are equal to zero or less than zero would be 3/6 or 50% How can I do this in Excel? Try this: =3DCOUNTIF(range,"<=3D0") / COUNT(range) Change range to what you are using. Format the cell as percentage. Hope this helps. Pete On Jul 23, 4:46=A0pm, spowel4 <spow...@gmail.com> wrote: > I need to calculate the percentage of numbers, within a range of > cells, that are eq...