TechSpot

Microsoft Excel

By Darrenbilly
May 13, 2015
Post New Reply
  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. jobeard

    jobeard TS Ambassador Posts: 9,351   +622

    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. Darrenbilly

    Darrenbilly TS Enthusiast Topic Starter Posts: 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. jobeard

    jobeard TS Ambassador Posts: 9,351   +622

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

    jobeard TS Ambassador Posts: 9,351   +622

  6. cliffordcooley

    cliffordcooley TS Guardian Fighter Posts: 8,560   +2,901

    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.

Similar Topics

Add New Comment

You need to be a member to leave a comment. Join thousands of tech enthusiasts and participate.
TechSpot Account You may also...