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

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

Another way. Try this in C2 and fill down.

Formula:

Please Login or Register  to view this content.

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

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?

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

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.