Microsoft Excel

Darrenbilly

Posts: 163   +2
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!
 
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 ...
 
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?
 
AH, the contract number is consistent per customer - - yes -- let me play a bit.
 
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:
Back