George has attended:
Excel Intermediate course
PowerPoint Intermediate Advanced course
Virtual training delivery skills course
Excel Advanced course
Excel Advanced course
Effective Communication Skills course
Time Management course
Excel count total number of filled rows without counting duplica
I would like to know how many clients I have. Each client’s details are set out on a different row. The spreadsheet has multiple months and years set out chronologically from top to bottom. I update each client’s case if need be each month. Not every client features in each month.
Is there a simple way to add up how many clients I have which discounts repetition of the same client further down the spreadsheet? I only want to count each one once but I want to a total of every one that features at least once.
RE: Excel count total number of filled rows without counting dup
Hi George,
Thank you for the forum question.
I have got the information that you have Excel 365. If your version is 365, you have the Unique function.
The Unique function together with the Counta function can do what you want. If you have all the client names in the range A2:A500 type:
=Counta(Unique(A2:A500))
Please let me know, if you do not have Excel 365 or you cannot get this to work.
You can also copy the whole column of client names and paste the names in another column and remove duplicates (you have the remove duplicates tool on the Data tab in the Data Tools group). Then you will have a column with unique names only. You can then use the Counta function to count the range with unique client names.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
