# Microsoft Excel

By Darrenbilly ยท 5 replies
May 13, 2015
1. Good afternoon, all

I have an Excel question.

I have a set of date (over 4000 cells) The format is consistent, H-00000. Each sequence is a contract number, every time it repeats it represents a terminal which a customer had, so if H-02999 appears 5 times in the list, the customer had 5 terminals. I need to calculate how many contracts had 1, 2, 3, 4, 5, 6+ terminals.

Can anyone help?

Thanks!

2. ### jobeardTS AmbassadorPosts: 10,300   +788

Select all columns, data->sort, select the customer id, contract, date, sort
the customer.contract_num will all be consecutive.

you can then at least be well ordered. If I get some time, I'll play with using a formula and get back to you ...

3. ### DarrenbillyTS EnthusiastTopic StarterPosts: 161

I have just the one column, I have removed all other data. I can only find out the number of terminals by using that list. Is it still possible?

4. ### jobeardTS AmbassadorPosts: 10,300   +788

AH, the contract number is consistent per customer - - yes -- let me play a bit.

6. ### cliffordcooleyTS Guardian FighterPosts: 9,131   +3,243

COUNTIF(range,criteria) formula: "=COUNTIF(A:A,A1)"

The formula searches all cells in column A and counts them if they equal the value in A1. Place the formula in B1 next to A1 and fill down. Fill down will adjust the formula for the next value in column A and place the count in column B.

Afterward you can filter column B to only list the values you want to see.

Last edited: May 20, 2015
SNGX1275 likes this.