Count number of occurances of numbers

wjoyner

New Member
Joined
Feb 17, 2002
Messages
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.
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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".
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,140
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top