Count number of occurances of numbers

wjoyner · Feb 18th, 2002, 01:11 PM

I would like to count the number of times series of numbers appears in a column. For example: 123 is listed 2 times and 345 is listed 4 times. I would like to be able to sum the 2 (events)and 4 (events) for a value of 6 and place that value in a cell for additional use. Anyone know of a formula to do this? I'm trying to see how many times a ticket is responded to more than once during the reporting period.

Chris Davison · Feb 18th, 2002, 01:56 PM

you could countif each criteria and add them together

say b1 contains "123" and b2 contains "345"

and your ticket data is in range a1 to a20 :

=COUNTIF(A1:A20,B1)+COUNTIF(A1:A20,B2)

(I'd put your two criteria in their own cells otherwise you'd have to manually adjust your formula each time you want to change what you're looking for)

_________________
Hope this helps,
Chris

[ This Message was edited by: Chris Davison on 2002-02-18 12:57 ]

[ This Message was edited by: Chris Davison on 2002-02-18 12:57 ]

wjoyner · Feb 18th, 2002, 02:07 PM

Good start I believe. However; I will not know what the numbers are in advance. The numbers that may be common will be random ticket numbers. For example : My On-Call person takes a call at 8:00 AM on ticket 55624, then later on the next day, the ticket is addressed again at 4:17 PM. (Probably hadn't been resolved yet). The ticket has now been reviewed 2 times over the course of 2 days. I want to be able to see how many times ticket 55624 has been addressed before it is finally closed. This could go on for 15 - 100 diffrent tickets during any given period. The ticket numbers would be random in that the customer calls are assigned a number when received, but may be worked by several diffrent people before finally being closed. Each person making an entry in the log for their efforts.

Mark W. · Feb 18th, 2002, 02:33 PM

Quote:

On 2002-02-18 13:07, wjoyner wrote:
Good start I believe. However; I will not know what the numbers are in advance. The numbers that may be common will be random ticket numbers. For example : My On-Call person takes a call at 8:00 AM on ticket 55624, then later on the next day, the ticket is addressed again at 4:17 PM. (Probably hadn't been resolved yet). The ticket has now been reviewed 2 times over the course of 2 days. I want to be able to see how many times ticket 55624 has been addressed before it is finally closed. This could go on for 15 - 100 diffrent tickets during any given period. The ticket numbers would be random in that the customer calls are assigned a number when received, but may be worked by several diffrent people before finally being closed. Each person making an entry in the log for their efforts.

I'd recommend that you create a PivotTable that lists 'ticket number' in the ROW area and Count of 'ticket number' in the DATA area. That leaves one question: How do you signify that the ticket is closed? Do you "flag" it in a separate column?

wjoyner · Feb 18th, 2002, 02:41 PM

Yes a seperate column is used to flag whether the ticket is closed. The columns include date, number, small issue field, persons name (working ticket (making entry)), and finally status. Status using one of three values. 'O' Open, 'X' Closed or '-' for pending.

Mark W. · Feb 18th, 2002, 02:49 PM

Quote:

On 2002-02-18 13:41, wjoyner wrote:
Yes a seperate column is used to flag whether the ticket is closed. The columns include date, number, small issue field, persons name (working ticket (making entry)), and finally status. Status using one of three values. 'O' Open, 'X' Closed or '-' for pending.

Then you could include 'Status' in the PivotTable's PAGE area and set it's value to "O".

JerryWMac · Feb 20th, 2002, 07:24 PM

Hi Mark, this is an add-In. I ahve a similar problem to the above, I used your formula, or close to it, modified to fit in the data area. I can not get the durn thing to feed out.
Formula:
=COUNTIF(F1:K966,A2)
I continually get the same output, 0 which is null.
I want to count how many times the number in A2 is matching the numbers in the data.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Feb 18th, 2002, 01:11 PM	#1
wjoyner New Member Join Date: Feb 2002 Location: Wayne Joyner - Omaha, NE Posts: 9	I would like to count the number of times series of numbers appears in a column. For example: 123 is listed 2 times and 345 is listed 4 times. I would like to be able to sum the 2 (events)and 4 (events) for a value of 6 and place that value in a cell for additional use. Anyone know of a formula to do this? I'm trying to see how many times a ticket is responded to more than once during the reporting period.

Feb 18th, 2002, 01:56 PM	#2
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	you could countif each criteria and add them together say b1 contains "123" and b2 contains "345" and your ticket data is in range a1 to a20 : =COUNTIF(A1:A20,B1)+COUNTIF(A1:A20,B2) (I'd put your two criteria in their own cells otherwise you'd have to manually adjust your formula each time you want to change what you're looking for) _________________ Hope this helps, Chris [ This Message was edited by: Chris Davison on 2002-02-18 12:57 ] [ This Message was edited by: Chris Davison on 2002-02-18 12:57 ]

Feb 18th, 2002, 02:07 PM	#3
wjoyner New Member Join Date: Feb 2002 Location: Wayne Joyner - Omaha, NE Posts: 9	Good start I believe. However; I will not know what the numbers are in advance. The numbers that may be common will be random ticket numbers. For example : My On-Call person takes a call at 8:00 AM on ticket 55624, then later on the next day, the ticket is addressed again at 4:17 PM. (Probably hadn't been resolved yet). The ticket has now been reviewed 2 times over the course of 2 days. I want to be able to see how many times ticket 55624 has been addressed before it is finally closed. This could go on for 15 - 100 diffrent tickets during any given period. The ticket numbers would be random in that the customer calls are assigned a number when received, but may be worked by several diffrent people before finally being closed. Each person making an entry in the log for their efforts.

Feb 18th, 2002, 02:41 PM	#5
wjoyner New Member Join Date: Feb 2002 Location: Wayne Joyner - Omaha, NE Posts: 9	Yes a seperate column is used to flag whether the ticket is closed. The columns include date, number, small issue field, persons name (working ticket (making entry)), and finally status. Status using one of three values. 'O' Open, 'X' Closed or '-' for pending.

Feb 20th, 2002, 07:24 PM	#7
JerryWMac New Member Join Date: Feb 2002 Location: CD Victoria, Mexico Posts: 1	Hi Mark, this is an add-In. I ahve a similar problem to the above, I used your formula, or close to it, modified to fit in the data area. I can not get the durn thing to feed out. Formula: =COUNTIF(F1:K966,A2) I continually get the same output, 0 which is null. I want to count how many times the number in A2 is matching the numbers in the data.