Count only number of values within duplicate records

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

3. 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. 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. Re: Count only number of values within duplicate records

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

6. 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. 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. Re: Count only number of values within duplicate records

Originally Posted by davidharper2005
.............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. 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. Re: Count only number of values within duplicate records

Originally Posted by davidharper2005
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. 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. 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. 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.

Users Browsing this Thread

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

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