+ Reply to Thread
Results 1 to 13 of 13

Count only number of values within duplicate records

  1. #1
    Registered User
    Join Date
    05-03-2011
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    35

    Count only number of values within duplicate records

    I am looking for a formula that will allow me to count how many services a particular customer receives. For example, in the spreadsheet which contains all client records each customer is listed on separate rows for each service they receive. Therefore, I am looking for a formula that will allow me to count the number of services within duplicate records in the name of client column. The objective is to show how many people receive 1 service, 2 services, 3 services and so on.

    I have attached a sample spreadsheet with how it would look like. My plan is to then set up another excel formula to count number of people with 1 service and so on in a separate tab.

    Cheers for any help you can provide.

    Excel Forum Posting.xlsx

  2. #2
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    1,899

    Re: Count only number of values within duplicate records

    do you have to have a formula or will a pivot table do? Select the two columns, insert pivot table, put Client name in the row and the services in the values and make sure it is set to 'count' summary
    Happy with my advice? Click on the * reputation button below

  3. #3
    Registered User
    Join Date
    05-03-2011
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    35

    Re: Count only number of values within duplicate records

    It's a formula I'm looking for as the client datasheet can change over time and I'm looking to automate the process. Any advice?

  4. #4
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    1,899

    Re: Count only number of values within duplicate records

    A pivot table can be refreshed BUT if you want a formula I'd do it this way

    Firstly create a unique list in say column E by using this array formula in E2 then copy down

    E2 =IFERROR(INDEX($A$2:$A$6, MATCH(0, COUNTIF(E1:$E$1, $A$2:$A$6), 0)),"") - Its an array formula so once you've put it in use Cntl+Shift +Enter and you'll know its right by the curly brackets and then copy down column E

    then in F2 use this formula (normal not an array)

    F2 =COUNTIF($A$2:$A$6,E2) and then copy down column F

    this will give you a list of people and the count. I assume (based on your data) that the services are listed only once, otherwise you'll get the wrong result. Also in your data Joe Bloggs is spelt differently so which ever way you do this it will pick that up as two different people

  5. #5
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,627

    Re: Count only number of values within duplicate records

    Another way. Try this in C2 and fill down.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Dave

  6. #6
    Registered User
    Join Date
    05-03-2011
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    35

    Re: Count only number of values within duplicate records

    Thanks for your suggestions. I'm trying to avoid an array formula and went with FlameRetired's one. Flameretired, do you need to make reference to the B column (where the services are listed?) in your
    formula? I should have mentioned there are some rows where there is no service - I've attached a revised spreadsheet.

    What would be the formula so that it's only counting the number of times that a service is appearing for each client?

    Excel Forum Posting.xlsx

  7. #7
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    1,899

    Re: Count only number of values within duplicate records

    Try this variation on Flameretired formula

    Please Login or Register  to view this content.
    note the red section is an error in Flameretired original formula that I corrected (I think - Flameretired will let me know if its not I'm sure) and the green bit is the bit that captures the column B AND column A

  8. #8
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,627

    Re: Count only number of values within duplicate records

    Quote Originally Posted by davidharper2005 View Post
    .............What would be the formula so that it's only counting the number of times that a service is appearing for each client?

    Attachment 407018
    I believe Crooza took care of it.

    There is one thing you didn't mention. John Smith has one each of a unique (for him) Service. Will there be multiple instances for John Smith with duplicated Services (for example 2 Service A's)? What do you want to do with those?

  9. #9
    Registered User
    Join Date
    05-03-2011
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    35

    Re: Count only number of values within duplicate records

    I am trying to avoid using an array formula as I have a lot of formulas in the spreadsheet and trying to automate it as much as possible and eliminate the need for a Cntl+Shift +Enter command. Is there a formula that automates this?

    Yes there are instances of clients who receive more than one of the same type of service. However, I don't need the formula to distinguish between those but to count any time a service name is listed next to a client name (for example if John Smith was to receive two Service A - the formula would return 2 for the number of services received).

    Thanks for any help you can provide.

  10. #10
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    1,899

    Re: Count only number of values within duplicate records

    Quote Originally Posted by davidharper2005 View Post
    I am trying to avoid using an array formula as I have a lot of formulas in the spreadsheet and trying to automate it as much as possible and eliminate the need for a Cntl+Shift +Enter command. Is there a formula that automates this?

    Yes there are instances of clients who receive more than one of the same type of service. However, I don't need the formula to distinguish between those but to count any time a service name is listed next to a client name (for example if John Smith was to receive two Service A - the formula would return 2 for the number of services received).

    Thanks for any help you can provide.
    The solution I provided (based on FlameRetired original solution) will do this. There's no need for further changes

  11. #11
    Registered User
    Join Date
    05-03-2011
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    35

    Re: Count only number of values within duplicate records

    Crooza, I apologise I didn't see your message before. I just used your formula and it was pretty much accurate but returned me the value for each time their names were mentioned. I just changed the "<>"&"" part of your formula to "*?" and it solved the problem I think. Thanks for your help!

  12. #12
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    1,899

    Re: Count only number of values within duplicate records

    Do you have spaces in column B where they appear blank? A space will be viewed as a non blank and as such will return a higher number. If you delete the 'blank' cells to make the truly blank my formula should work.

  13. #13
    Registered User
    Join Date
    05-03-2011
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    35

    Re: Count only number of values within duplicate records

    You're right Crooza. I forgot to mention there were formulas in that column and some of them returned blanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Arrange serial number for duplicate records
    By winmaxservices1 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-22-2015, 07:45 AM
  2. [SOLVED] Count the number of duplicate values
    By DixieDoll11 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 12-02-2014, 05:12 PM
  3. Count single records when duplicate records exist
    By omeng414 in forum Excel General
    Replies: 4
    Last Post: 11-27-2014, 03:42 PM
  4. Trying to count the number of occurrences of duplicate values
    By rahworks in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-11-2014, 02:55 PM
  5. Replies: 1
    Last Post: 03-28-2013, 01:48 PM
  6. Count duplicate records
    By stats09 in forum Excel General
    Replies: 9
    Last Post: 03-23-2010, 03:26 AM
  7. count duplicate records in a group
    By Mparekh in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-21-2005, 10:10 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1